Count if formula for multiple columns

[partyrental]

Hi
I am need help with a formula

I am trying to create a formula for Cell H3 on attached worksheet,
If the drop down in G3 is a month i want the formula to count all the "true" data in column B, and if the Drop down is a quarter(like Q3), i want it to count all "True" cells in column C.

I have no clue as to what formula i can write that would sort between columns B&C to count the "true" cells.

Please see attached workbook for test data
Test Data.xlsx

[loginjmor]

Hi -

A little clarification: If it's a month, say March, you want to count up the TRUE values only for dates in March? And if it's a quarter, say 3rd quarter, you only want to count the TRUE values that occur in Q3 (July, August, September)? Is that correct? If so, you don't really need two columns, unless there is something else different about the months versus the quarters?

[Special-K]

Mmm...thinking

[PaulM100]

Updated in the file, as I cannot post the formula
No need for column B and C, but you need your date in A1 to be actual date. And you can format it as mmm-yyyy

[Special-K]

=SUMPRODUCT((TEXT(A$5:A$1000,"mmm-yyyy")=G3)*(B5:B1000=TRUE))+SUMPRODUCT(("Q"&(ROUNDUP(MONTH(A$5:A$1000)/3,0)&"-"&YEAR(A$5:A$1000))=G3)*(C5:C1000=TRUE))

[Glenn Kennedy]

You would not want to see the monster that I was spawning. truly awful!!

[partyrental]

That is correct,
The users of the file, will have a drop down menu, to select either a Month or the Quarter. I didn't know how to show the data in 1 column hence the 2 columns,

[partyrental]

Thanks so much for this, I looked at the file, it works perfectly for Month calculation but, i doesn't seem to be counting the correct amount for the quarterly data

[PaulM100]

May falls in Quarter 2(April,May,June). If you filter on those 3 months in 2019, column A, is gonna return 7 results. You C column is wrong

Edit: If you are gonna use a month, but a different quarter(Q3 in your example, then use the other formula provided by Special-K or combine mine with his.

[partyrental]

=SUMPRODUCT((TEXT(A$5:A$1000,"mmm-yyyy")=G3)*(B5:B1000=TRUE))+SUMPRODUCT(("Q"&(ROUNDUP(MONTH(A$5:A$1000)/3,0)&"-"&YEAR(A$5:A$1000))=G3)*(C5:C1000=TRUE))

This formula worked perfectly, Thank you. i have an additional ask, if i wanted to add another criteria, like user name,so i want to count a specific users during either the selected timeframe( either month or quarter). for example: John had 4 "TRUE" is Q3-2019. how would i do that
Attachment 644973

[Gregb11]

I could not download your file, so in the file attached, I inserted at Col B and typed in "John" in some cells. In cell I3 I have this formula:

Please Login or Register  to view this content.

[menem]

Just find "Q" in drop down list.
(Because no months is contain 'Q')

H3
=IFERROR(IF(FIND("Q",G3),COUNTIF($C:$C,TRUE)),COUNTIF($B:$B,TRUE))

Regards.

[protonLeah]

heres another one wrt names:

Please Login or Register  to view this content.

[partyrental]

Thank you, it worked perfectly