Reputation: 3
I am trying to count the same value over multiple columns but only if they appear in a certain date in the format 'YYYY-MM'
I have gotten so far through bloody mindedness (code below) but it only looks at one column and counts them when I want to look at both columns but only if they fall on a certain date.
As an example, I have 3 columns. The first column 'A' has YYYY-MM values, the second 'B' is called Partner1 filled with names and the third column 'C' is called Partner2 again filled with names. I want to find out how many times 'Dave' (F2) appears in both columns on a certain date.
e.g. How many times has Dave been a partner in 2016-04 (A17)?
Thanks for reading.
=COUNTIFS(A2:A10,A17,INDEX(B2:C10,,MATCH(F2,B2:B10,0)),"Dave")
Date: 2016-04
no. times Dave has a partner: 3
Upvotes: 0
Views: 46
Reputation: 8240
You could use:
=SUMPRODUCT((A2:A7=A17)*(B2:B7="Dave")*(C2:C7="Dave")*1)
=SUMPRODUCT((A2:A7="2016-04")*ISNUMBER(MATCH(B2:B7,{"Dave","Lisa"},0))*ISNUMBER(MATCH(C2:C7,{"Dave","Lisa"},0)))
Results:
Upvotes: 0
Reputation: 371
Use 2 COUNTIFS
like this -
=COUNTIFS(A2:A10, A17, B2:B10, "Dave") + COUNTIFS(A2:A10, A17, C2:C10, "Dave")
Upvotes: 1
Reputation: 5902
You should use a construct like below.
=COUNTIFS(A2:A10,A17,B2:B10,"Dave",C2:C10,"Dave")
Upvotes: 0