conditional count

AaronJones

Hello good people,

I'm simply trying to come up with a formula that counts the occurrences of a specified value from a drop down list column (even if there are multiple values selected in cell within that column). There are 2 other conditions as well with designated columns: specified date and person.

This formula is drawing from another sheet and after about 30 minutes of research this was the closest i could come up with:

=SUM(IF((ISNUMBER(SEARCH("Completed", {BMDA TAP QA Sheet Range 5}))) * ({BMDA TAP QA Sheet Range 1}="2023-02") * ({BMDA TAP QA Sheet Range 2}="John Doe"),1,0))

I've tried multiple variations of countifs, has, contains, if........ I don't know.

any help would be greatly appreciated :)

Paul McGuinness

Hi @AaronJones

This formula will hopefully deliver what you need based on your description and the formula you provided above.

=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed", @cell),{BMDA TAP QA Sheet Range 1},"2023-02",{BMDA TAP QA Sheet Range 2},"John Doe")

This will only count where all 3 criteria are met.

Hope that helps

Thanks

Paul

AaronJones

i couldn't get this work either. I'm using cell references within my calc sheet. I changed the "contains" reference to verbatim, however, and it didn't seem to impact it:

=COUNTIFS({BMDA TAP QA Sheet Range 5}, CONTAINS("Completed",@cell),{BMDA TAP QA Sheet Range 1}, [Column3]55, {BMDA TAP QA Sheet Range 2}, [Column4]36)

any thoughts? thanks in advance!

Paul Newcome

Are you getting an error or an inaccurate count?

AaronJones

i'm getting unparseable with this one

Paul Newcome

I don't see any issues with the syntax you posted. Are you able to provide a snippet of the formula actually in the sheet similar to below?

AaronJones

Thanks for the assistance! I was able to get it to work with the formula below (which i swear i had already tried haha). The countifs previously wasn't returning what the countm function is for....however, with this combination it is somehow counting the elements appropriately.