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 |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M |
---|
1 | Name | Citizen | Age | Birthday | Father Birthday | Mother Birthday | | | Citizen | Min age | Month | Father Month | Mother month |
---|
2 | a | Y | 23 | 1/1/2000 | 10/10/1975 | 7/7/1979 | | | Y | 25 | 11/1/1990 | 10/1/1975 | 3/1/1966 |
---|
3 | b | Y | 33 | 2/2/1990 | 2/2/1965 | 3/3/1966 | | | | | | | |
---|
4 | c | Y | 38 | 3/3/1985 | 10/10/1961 | 10/11/1960 | | | Count | | | | |
---|
5 | d | N | 52 | 10/10/1970 | 5/5/1949 | 5/5/1949 | | | 2 | | | | |
---|
6 | e | Y | 32 | 11/11/1990 | 1/1/1965 | 2/2/1966 | | | | | | | |
---|
7 | f | Y | 23 | 10/22/1999 | 10/11/1969 | 7/24/1969 | | | | | | | |
---|
8 | g | N | 34 | 10/1/1988 | 10/31/1965 | 8/8/1966 | | | | | | | |
---|
|
---|
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