count in multiple ranges

[babuda]

Hello,

Can someone please take a look at the formulas in column cells G28..G33 and help me where I went wrong.I have attached the file please.

Thanks in advance
Babuda

[teylyn]

Hi Babuda

in cell G28 you compare with the wrong severity. The formula should be

=SUMPRODUCT(($F$2:$F$25="Open")*($H$2:$H$25=1)*(($G$2:$G$25)<=10))

For the next lines, your logic is a bit wrong. You don't need an AND() formula, because SUMPRODUCT already does that.

you want to sum if the value
- is open
- is Severity 1
- is 11 or more
- is less than 21

in SUMPRODUCT syntax:

=SUMPRODUCT((is open)*(is severity 1)*(is >=11)*(is <21))

So your Sumproduct formula must be

=SUMPRODUCT(($F$2:$F$25="Open")*($H$2:$H$25=1)*($G$2:$G$25>=11)*($G$2:$G$25<21))

next line: you want to sum if
- is open
- is Severity 1
- is 21 or more
- is less than 31

So your formula must be

=SUMPRODUCT(($F$2:$F$25="Open")*($H$2:$H$25=1)*($G$2:$G$25>=21)*($G$2:$G$25<31))

Can you see it?

[babuda]

Thank you Teylyn for your help, I really appreciate your time