New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Countifs With More Than 1 Range

Macros, VBA, Excel Automation, etc.

Countifs With More Than 1 Range

Postby Mami » Mon Apr 04, 2016 8:03 am

Hi Guys,

I am trying to generate a code using CountIfs function for more than one ranges. My code works well when selecting the ranges as shown below, :

Cells(3, 19).Value = Application.WorksheetFunction.CountIfs(Range("A1:C3"), "=" & 1, Range("B1:D3"), "=" & 1, Range("A2:C4"), "=" & 1, Range("B2:D4"), "=" & 1)

However, this form of the same line is not working for more than one ranges:

Cells(3, 19).Value = Application.WorksheetFunction.CountIfs(Range(Cells(3, 1), Cells(3, 3)), "=" & 1, Range(Cells(1, 2), Cells(3, 4)), "=" & 1, Range(Cells(2, 1), Cells(4, 3)), "=" & 1, Range(Cells(2, 2), Cells(4, 4)), "=" & 1)

Any suggestions on how to solve this?

Thanks
  • 0

Mami
Rookie
 
Posts: 2
Joined: Apr 4, 2016
Reputation: 0
Excel Version: 2013

Re: Countifs With More Than 1 Range

Postby pecoflyer » Mon Apr 04, 2016 9:23 am

Hi and welcome to the board

please wrap code with tags as requested ( see text in red font). Thx
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Countifs With More Than 1 Range

Postby Mami » Tue Apr 05, 2016 12:29 am

Hi pecoflyer,
Thanks a lot. Here is the post with wrapped code:

I am trying to generate a code using CountIfs function for more than one ranges. My code works well when selecting the ranges as shown below, :

Code: Select all
Cells(3, 19).Value = Application.WorksheetFunction.CountIfs(Range("A1:C3"), "=" & 1, Range("B1:D3"), "=" & 1, Range("A2:C4"), "=" & 1, Range("B2:D4"), "=" & 1)


However, this form of the same line is not working for more than one ranges:

Code: Select all
Cells(3, 19).Value = Application.WorksheetFunction.CountIfs(Range(Cells(3, 1), Cells(3, 3)), "=" & 1, Range(Cells(1, 2), Cells(3, 4)), "=" & 1, Range(Cells(2, 1), Cells(4, 3)), "=" & 1, Range(Cells(2, 2), Cells(4, 4)), "=" & 1)


Any suggestions on how to solve this?

Thanks
  • 0

Mami
Rookie
 
Posts: 2
Joined: Apr 4, 2016
Reputation: 0
Excel Version: 2013


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 46 guests