COUNTIFS and OR Statement

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
Does anyone know how to use COUNTIFS and OR statements together? I've only see COUNTIF and OR statements but not COUNTIFS. I'm trying to count the number of times a particular data set has US Citizenship (Y or N), is over 25 years in age, and is either (i.e. OR) born in October 2022, or has a father that was born on October 2022, or has a mother that was born in October 2022.

I have columns for US Citizenship (Y/N), age, birthdate of individual, birthdate of individual's father, and birthdate of individual's mother.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Well, I assume your example is just an example, since otherwise I can tell the answer is 0. No one can be born in October 2022, and be over 25. Given that, there are some complicated ways to use COUNTIFS to incorporate an OR, but you're better off using SUMPRODUCT or FILTER. For example:

Book1
ABCDEFGHIJKLM
1NameCitizenAgeBirthdayFather BirthdayMother BirthdayCitizenMin ageMonthFather MonthMother month
2aY231/1/200010/10/19757/7/1979Y2511/1/199010/1/19753/1/1966
3bY332/2/19902/2/19653/3/1966
4cY383/3/198510/10/196110/11/1960Count
5dN5210/10/19705/5/19495/5/19492
6eY3211/11/19901/1/19652/2/1966
7fY2310/22/199910/11/19697/24/1969
8gN3410/1/198810/31/19658/8/1966
Sheet1
Cell Formulas
RangeFormula
I5I5=ROWS(FILTER(A2:A8,(B2:B8=I2)*(C2:C8>=J2)*((EOMONTH(D2:D8+0,0)=EOMONTH(K2,0))+(EOMONTH(E2:E8+0,0)=EOMONTH(L2,0))+(EOMONTH(F2:F8+0,0)=EOMONTH(M2,0)))))
C2:C8C2=DATEDIF(D2,TODAY(),"y")


This formula encompasses the basic outline of your example. You can use the same selected date in K2:M2 if you want. Using a formulation like this requires some knowledge of Boolean formulas, which you can research easily.
 
Upvote 0
Well, I assume your example is just an example, since otherwise I can tell the answer is 0. No one can be born in October 2022, and be over 25. Given that, there are some complicated ways to use COUNTIFS to incorporate an OR, but you're better off using SUMPRODUCT or FILTER. For example:

Book1
ABCDEFGHIJKLM
1NameCitizenAgeBirthdayFather BirthdayMother BirthdayCitizenMin ageMonthFather MonthMother month
2aY231/1/200010/10/19757/7/1979Y2511/1/199010/1/19753/1/1966
3bY332/2/19902/2/19653/3/1966
4cY383/3/198510/10/196110/11/1960Count
5dN5210/10/19705/5/19495/5/19492
6eY3211/11/19901/1/19652/2/1966
7fY2310/22/199910/11/19697/24/1969
8gN3410/1/198810/31/19658/8/1966
Sheet1
Cell Formulas
RangeFormula
I5I5=ROWS(FILTER(A2:A8,(B2:B8=I2)*(C2:C8>=J2)*((EOMONTH(D2:D8+0,0)=EOMONTH(K2,0))+(EOMONTH(E2:E8+0,0)=EOMONTH(L2,0))+(EOMONTH(F2:F8+0,0)=EOMONTH(M2,0)))))
C2:C8C2=DATEDIF(D2,TODAY(),"y")


This formula encompasses the basic outline of your example. You can use the same selected date in K2:M2 if you want. Using a formulation like this requires some knowledge of Boolean formulas, which you can research easily.
Thank you @Eric W. Yes, those are dummy examples but I did follow what you did and try to replicate mine to closely match with yours. It looks like I'm able to capture the ORs across the various dates but not the other attributes as it errors out on me (#n/a) when in reality a number should appear. Here's the equation that I have in my google-sheets:

=ROWS(FILTER('Tab1'!$A:$A,('Tab1'!$B:$B=$U17)*((EOMONTH('Tab1'!$N:$N+0,0)=EOMONTH(AV$13,0))+(EOMONTH('Tab1'!$Q:$Q+0,0)=EOMONTH(AV$13,0))+(EOMONTH('Tab1'!$T:$T+0,0)=EOMONTH(AV$13,0)))))

So a few call outs on mine.
- I use full columns in a different raw data worksheet called 'Tab1' versus specifying a very specific set of rows on the same worksheet, as in your example. I'm hoping that's not the reason for my error.
- The part in my formula ('Tab1'!$B:$B=$U17) is checking another formula in column B on the worksheet `Tab1` is equal "Yes". Basically its the (B2:B8=I2) in your equation. When I remove this part of the formula and just check dates, an accurate number shoots out. Not sure why having this formula in the overall function is causing the error.
- I haven't even started adding anything my version of your "(C2:C8>=J2)" part of the equation until I can figure the above out.
- You're probably noticing that I'm also always referencing the AV$13 cell for all my dates. That is because I'm now actually seeing if there's anyone with the same "processing month" (not birthdate). So basically, I want to know how many US Citizens, overage the age of 25, with either their mom, dad, or self having processed through the system in October 2022 (AV$13 = the first day in Oct as 10/1/2022).

Any suggestions?
Tom
 
Upvote 0
I wish you'd mentioned Google Sheets before. There are similarities, but there are differences. Sometimes the tiny differences can make a valid formula in Excel not work in GS, and vice versa. I don't know enough about GS to identify those differences. Still, here are a few comments I have on your questions.

Thank you @Eric W. Yes, those are dummy examples but I did follow what you did and try to replicate mine to closely match with yours. It looks like I'm able to capture the ORs across the various dates but not the other attributes as it errors out on me (#n/a) when in reality a number should appear. Here's the equation that I have in my google-sheets:

=ROWS(FILTER('Tab1'!$A:$A,('Tab1'!$B:$B=$U17)*((EOMONTH('Tab1'!$N:$N+0,0)=EOMONTH(AV$13,0))+(EOMONTH('Tab1'!$Q:$Q+0,0)=EOMONTH(AV$13,0))+(EOMONTH('Tab1'!$T:$T+0,0)=EOMONTH(AV$13,0)))))
This appears OK.
So a few call outs on mine.
- I use full columns in a different raw data worksheet called 'Tab1' versus specifying a very specific set of rows on the same worksheet, as in your example. I'm hoping that's not the reason for my error.
I can't say for sure since it's GS. In Excel using whole column references can be bad since it requires looking at over a million rows, when sometimes far fewer rows will work. I don't know about GS, it might be able to detect the last used row. You can decide if the performance is acceptable with the whole columns. It may be worth experimenting with actual row numbers if you still get an error.
- The part in my formula ('Tab1'!$B:$B=$U17) is checking another formula in column B on the worksheet `Tab1` is equal "Yes". Basically its the (B2:B8=I2) in your equation. When I remove this part of the formula and just check dates, an accurate number shoots out. Not sure why having this formula in the overall function is causing the error.
Hard to say without seeing your sheet. My first guess is that the condition ( ="Yes" ) is not being evaluated as TRUE for some reason. Maybe there's an extra space, maybe capitalization counts, maybe you're looking at the wrong column. If GS has the equivalent of "Evaluate Formula", you can check with that. You can also try ISNUMBER(SEARCH("Y",'Tab1'!$B:$B)) if GS supports it, to just look for the letter Y.
- I haven't even started adding anything my version of your "(C2:C8>=J2)" part of the equation until I can figure the above out.
Logical, get one issue resolved before starting another.
- You're probably noticing that I'm also always referencing the AV$13 cell for all my dates. That is because I'm now actually seeing if there's anyone with the same "processing month" (not birthdate). So basically, I want to know how many US Citizens, overage the age of 25, with either their mom, dad, or self having processed through the system in October 2022 (AV$13 = the first day in Oct as 10/1/2022).
Processing month makes more sense than birthdate, I wondered about that.
Any suggestions?
Tom
You seem to have the basic idea, just try to work out the issues. Good luck!
 
Upvote 0
I wish you'd mentioned Google Sheets before. There are similarities, but there are differences. Sometimes the tiny differences can make a valid formula in Excel not work in GS, and vice versa. I don't know enough about GS to identify those differences. Still, here are a few comments I have on your questions.


This appears OK.

I can't say for sure since it's GS. In Excel using whole column references can be bad since it requires looking at over a million rows, when sometimes far fewer rows will work. I don't know about GS, it might be able to detect the last used row. You can decide if the performance is acceptable with the whole columns. It may be worth experimenting with actual row numbers if you still get an error.

Hard to say without seeing your sheet. My first guess is that the condition ( ="Yes" ) is not being evaluated as TRUE for some reason. Maybe there's an extra space, maybe capitalization counts, maybe you're looking at the wrong column. If GS has the equivalent of "Evaluate Formula", you can check with that. You can also try ISNUMBER(SEARCH("Y",'Tab1'!$B:$B)) if GS supports it, to just look for the letter Y.

Logical, get one issue resolved before starting another.

Processing month makes more sense than birthdate, I wondered about that.

You seem to have the basic idea, just try to work out the issues. Good luck!
Thank you again.
 
Upvote 0

Forum statistics

Threads
1,223,308
Messages
6,171,331
Members
452,396
Latest member
ajl_ahmed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top