Combine COUNTIFS statement with SUMPRODUCT formula

[Brendan89]

So, I have a formula that returns a total amount of dates that are older than 30 days:

=SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000<TODAY()-30))

This works great, however I also need to combine a COUNTIFS statement so it only checks for the date if certain criteria matches in another column (G).
=COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"})

I have no idea how to combine these into one formula to do this at the same time? Can anyone help?

 

[Johnny C]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

Maybe I'm reading this wrong but isn't it just a case of
=IF(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"})=x,SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000 ...,0)
where x is your criteria?

 

[Brendan89]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

Maybe I'm reading this wrong but isn't it just a case of
=IF(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"})=x,SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000 ...,0)
where x is your criteria?

Hello Johnny,

For some reason it didn't post up the end of the SUMPRODUCT formula but if I could make x below to just =TRUE that returns a TRUE/FALSE statement rather than a % of cells that fall into the range?

=IF(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"})=x,SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000<TODAY()-30))

Sorry, I'm not too great with this stuff.

 

[Brendan89]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

... (Risks!H3:H1000<TODAY()-30)) 8Didnt copy the end of the formula a second time!

 

[FormR]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

=SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000<today()-30))

Hi, if you have < symbols then the board sometimes thinks it's HTML and tries to parse it - to get around this put spaces after any of those symbols when posting to the forum. it looks like it should have been:

=SUMPRODUCT(--ISNUMBER(Risks!H3:H1000),--(Risks!H3:H1000 < TODAY()-30))

If I understand what you want then you can do it all with COUNTIFS() like this.

=SUM(COUNTIFS(Risks!G3:G1000,{"Identified","Assessed","Planned","In-Process"},Risks!H3:H1000,"<"&TODAY()-30))

</today()-30))

 

[Johnny C]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

the COUNTIFS bit is being tested for true or false, instead of =x use >0

 

[Johnny C]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

FormR's way is better if the SUMPRODUCT works on 1 column, but will not work if the SUMPRODUCT works on more than 1 column.
I couldn't see what was in the SUMPRODUCT so assumed you were testing a multicolumn range.

 

[Johnny C]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

Sorry, I'm not too great with this stuff.

If are aware of and can get your head around SUMPRODUCT vector form you're probably in the top 0.1% of Excel users!

 

[Brendan89]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

Thanks Guys the >0 seems to have worked great! Appreciate the help.

Hopefully can wrap my head around it a bit better in the future, was stuck on that a while!

Thanks for the tip on forum posting as well, will make sure to paste with the spaces in future.

 

[FormR]

Re: How To ... Combine COUNTIFS statement with SUMPRODUCT formula

Thanks Guys the >0 seems to have worked great!

Hi, do you want a count of the rows where the date is < 30 days from today and the value in same row in column G is one of "Identified","Assessed","Planned","In-Process"?