Search code examples
excelexcel-2007

Compare rows across 2 columns and count value once if it appears twice all within a date range

hope someone can help on this problem I've hit a brick wall with.

I'm creating a quarterly report, so I need to firstly check that the formula looks at the correct date range. I have been using the following so far:

=COUNTIFS($A:$A,">="&B$5,$A:$A,"<="&B$6,$D:$D, "YES")

This has been working great, but now I need to compare 2 columns within the date range and only count 1 of the YES's if it appears in both rows.

Not sure if that makes complete sense, so here's an example.

Example

In the above, I would need to look at the date range 03/02/16 to 06/02/16, compare column 1 and column 2 and only count the YES once if it appears in both columns. So in the above, the value would return 4.

And I'm using Excel 2007.

Any help would be much appreciated!

Thanks in advance

Chris

Solution

  • Assuming the dates are in B5 and B6 and the columns to check for yes are C and D:

    =SUMPRODUCT((A2:A9<=$B$6)*(A2:A9>=$B$5)*(( D2:D9="YES")+(C2:C9="YES")>0))