SOLVED

Comparing dates in categories.

Copper Contributor

Hello,

 

I am trying to only use a formula and not VBA. 

 

I have 3 different columns I need to use for my formula: A category, a due date, and an actual date. I need to be able to calculate how many entries for each category have an actual date that is later than the due date. 

 

With the formula =SUMPRODUCT((C2:C13>B2:B13)*1), I am able to determine the count for all of the parts that have an actual date past the due date. However, I am unable to find a formula that works with separating this information into each category. 

 

In my attached example, I see that there are 4 entries that are late using my above formula. 1 is in category A, 2 are in category B, and 1 is in category C. However, I do not have a formula that tells me this information for each category.

 

Can anyone help me with this? Thank you. 

2 Replies
best response confirmed by Justine714 (Copper Contributor)
Solution

Hello @Justine714,

 

try in cell A17

 

=SUMPRODUCT(($C$2:$C$13>$B$2:$B$13)*($A$2:$A$13=A$16))

 

copy to the right.

 

If this post solves your problem, please mark it as "Best Response".

@Ingeborg Hawighorst Thank you so much! That worked!

1 best response

Accepted Solutions
best response confirmed by Justine714 (Copper Contributor)
Solution

Hello @Justine714,

 

try in cell A17

 

=SUMPRODUCT(($C$2:$C$13>$B$2:$B$13)*($A$2:$A$13=A$16))

 

copy to the right.

 

If this post solves your problem, please mark it as "Best Response".

View solution in original post