COUNTIFS with Date Calculations

[F1akman]

I'm trying to count the number of rows where the difference between two dates are greater than 30 days in addition to other criteria.

Reported_Date	Resolved_Date	Group	Tech	Category	YYYYMM
43375.63483	43375.70711	PC-Group	Tech 1	Low	201810
43335.42803	43375.70961	NET-Group	Tech 2	Medium	201809

I can use COUNTIFS without issue to filter fields (Group, Tech, Category, YYYMM) but can't seem to work the date differential into the formula without using a helper field.

Is there a way to do this?

Thanks,

John

[Bernie Deitrick]

=sumproduct(((b2:b1000-a2:a1000)>30)*1)

[F1akman]

Sorry, I needed to clarify. I need to do this calculation along with filtering for the other fields.
=countifs(C2:C4,"PC-Group",F2:F4,201809)
but need to add the sumproduct formula to the mix or add the countifs to the sumproduct formula. Whichever works.

[Bernie Deitrick]

=SUMPRODUCT(((C2:C4="PC-Group")*(F2:F4=201809)*((B2:B4-A2:A4)>30))*1)

You can add as many conditions as you need by adding (range=x)* or ((Calc)=x)* sub-units to the SUMPRODUCT - just make sure your row counts match.

[José Augusto]

Try this

Please Login or Register  to view this content.

[F1akman]

Strange.

If I do
=SUM(C23-B23)
I get 31.98868. So the numbers work.

But if I do the
=SUMPRODUCT(C23:C30-B23:B30>30)
I get 0.

I tried this because I was getting 0 when trying the SUMPRODUCT with other criteria. This is my data. The first Date 1 is B23.

Index Date1 Date2
1 43274.69734 43306.68602
2 43304.68601 43305.71149
3 43304.61339 43315.68117
4 43304.59642 43304.70284
5 43304.52712 43307.46205
6 43304.49925 43305.45123
7 43304.4874 43305.58998
8 43304.45787 43332.37057

[F1akman]

I got it. Had to do =SUMPRODUCT(--(C23:C30-B23:B30>30)) Time to try the additional criteria.

[Bernie Deitrick]

When you do

=SUMPRODUCT(C23:C30-B23:B30>30)

There is no "product" to sum It is just an array of TRUE and FALSE values.

Using either

=SUMPRODUCT(--(C23:C30-B23:B30>30))

or

=SUMPRODUCT((C23:C30-B23:B30>30)*1)

Converts the TRUE/FALSE values to numbers (1s and 0s) which can then be summed.

[F1akman]

Thanks Bernie! I completely spaced on the *1
*facepalm*