In Excel: counting the same value in multiple columns by date recorded

Question

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

Answer 1

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:

Answer 2

Use 2 COUNTIFS like this -

=COUNTIFS(A2:A10, A17, B2:B10, "Dave") + COUNTIFS(A2:A10, A17, C2:C10, "Dave")

Answer 3

You should use a construct like below.

=COUNTIFS(A2:A10,A17,B2:B10,"Dave",C2:C10,"Dave")