Counting values with two different criteria

[Geoff_canada]

Hi there...

Could use some help.

I have a spreadsheet with multiple action items with due dates and owners. The due dates are in the J column and the owners in the I column.

I have created a countif statement to create an aging report. So for example, I count J column and count all records older than 90 days.... using the below formula

=COUNTIF(Open!$J$2:$J$387,"<"&TODAY()-90)

This returns a value of say 29. Now what I want to do, is out of that value of 29, I would like to know how many belong to certain owners (ie values in column I)

I have tried this formula

=SUMPRODUCT(Open!$J$2:$J$387,"<"&TODAY()-90)*(Open!I2:I445="John Doe")

But it doesn't work - get value error

Anyone have any suggestions?

 

[ZVI]

Hi,
Try this:
=SUMPRODUCT((Open!$J$2:$J$445<(TODAY()-90))*(Open!$I$2:$I$445="John Doe"))

 

[AlphaFrog]

Try something like this...

=SUMPRODUCT((Open!$J$2:$J$387 < TODAY()-90)*(Open!I2:I387="John Doe"))

The two criteria ranges have to be the same size.

 

[Geoff_canada]

thanks guys. I think it worked (tried what ZVI gave me)

one more question

=SUMPRODUCT((Open!$J$2:$J$445<(TODAY()-90))*(Open!$I$2:$I$445="John Doe"))

So this worked, putting the value of 10 in the C7 cell. Which is all action items for John Doe older than 90 days.

Now I am trying to fill C8 with John Doe's actions which are between 61-90 days old

=SUMPRODUCT((Open!$J$2:$J$445<(TODAY()-60)-C7)*(Open!$I$2:$I$445="John Doe"))

But this doesnt work...essentially i am trying to do same value...but minus out the value in c7

 

[ZVI]

Try that one:
=SUMPRODUCT((Open!$J$2:$J$445<(TODAY()-60))*(Open!$I$2:$I$445="John Doe"))-C7

 

[Geoff_canada]

thanks ZVI...that worked perfect

Just one last thing. I also have some formulas which check action items due to complete in 1-7 days....8-30 days or greater than 30 days

Which are as follows
=COUNTIF(Open!$J$2:$J$396,">="&TODAY())-COUNTIF(Open!$J$2:$J$396,">="&TODAY()+7)

=COUNTIF(Open!$J$2:$J$396,">="&TODAY())-COUNTIF(Open!$J$2:$J$396,">="&TODAY()+30)-SUM(B12) (where B12 is the outcome of the first formula)

=COUNTIF(Open!J2:J397,">="&TODAY())-SUM(B12:B13)

I am trying to do the exact same thing for upcoming actions items....

for next 7 days i did this
=SUMPRODUCT(COUNTIF(Open!$J$2:$J$396,">="&TODAY())-COUNTIF(Open!$J$2:$J$396,">="&TODAY()+7))*(Open!$I$2:$I$396="John Doe")

and it seems to work

But when I do 8-30 days as follows

=SUMPRODUCT(COUNTIF(Open!$J$2:$J$396,">="&TODAY())-COUNTIF(Open!$J$2:$J$396,">="&TODAY()+30))*(Open!$I$2:$I$396="John Doe")

It doesnt seem to report correct values....Any ideas?

 

[ZVI]

For today up to today+7 days:
=SUMPRODUCT( (Open!$J$2:$J$445>=TODAY()) * (Open!$J$2:$J$445<=(TODAY()+7)) * (Open!$I$2:$I$445="John Doe"))
or the same:
=SUMPRODUCT( --(Open!$J$2:$J$445>=TODAY()), --(Open!$J$2:$J$445<=(TODAY()+7)), --(Open!$I$2:$I$445="John Doe"))

For today+8 up to today+30 days:
=SUMPRODUCT( (Open!$J$2:$J$445>=(TODAY()+8)) * (Open!$J$2:$J$445<=(TODAY()+30)) * (Open!$I$2:$I$445="John Doe"))
or the same:
=SUMPRODUCT( --(Open!$J$2:$J$445>=(TODAY()+8)), --(Open!$J$2:$J$445<=(TODAY()+30)), --(Open!$I$2:$I$445="John Doe"))

For more than today +30 days:
=SUMPRODUCT( (Open!$J$2:$J$445>(TODAY()+30)) * (Open!$I$2:$I$445="John Doe"))
or the same:
=SUMPRODUCT( --(Open!$J$2:$J$445>=(TODAY()+30)), --(Open!$I$2:$I$445="John Doe"))

 

[Geoff_canada]

thanks for much ZVI

All working perfectly now. Your help is greatly appreciated

 

[ZVI]

Glad it helped,
Kind regards!