Help with COUNTIFS formula

[RetroMe]

Hi All,

I have the following formula:

=COUNTIFS(DATA!$H$2:$H$20000,">="&$D$3,DATA!$H$2:$H$20000,"<="&$E$3,DATA!$C$2:$C$20000,'NB Info'!$B7,DATA!$N$2:$N$20000,"New Business",DATA!$P2:$P20000,"N")-(K7)

But I know need the 'New Business' section to also take into account 'Transfer Business' or 'Rollover Business'.

So it looks like this:

Count If - H2:H20000 is >= D3
H2:H20000 is <= E3
C2:C20000 is = B7
N2:N20000 is = 'New Biz' or 'Transfer Biz' or Rollover Biz'

Hope that makes sense.

Thanks.

 

[RetroMe]

Sorry to bump so soon, but I need to figure this out in the next hour! Would really appreciate someones assistance.

 

[RetroMe]

Bump!

 

[barry houdini]

If you want those 3 exclusively try

=SUM(COUNTIFS(DATA!$H$2:$H$20000,">="&$D$3,DATA!$H$2:$H$20000,"<="&$E$3,DATA!$C$2:$C$20000,'NB Info'!$B7,DATA!$N$2:$N$20000,{"New Business","Transfer Business","Rollover Business"},DATA!$P2:$P20000,"N"))-K7

Or if those are the only possible entries that end with "Business" then you could use a wildcard, e.g.

=COUNTIFS(DATA!$H$2:$H$20000,">="&$D$3,DATA!$H$2:$H$20000,"<="&$E$3,DATA!$C$2:$C$20000,'NB Info'!$B7,DATA!$N$2:$N$20000,"*Business",DATA!$P2:$P20000,"N")-K7

 

[RetroMe]

Barry, works perfectly thank you.

One more question, how would it work it I want to do a sum rather than a count?

 

[barry houdini]

For a sum change COUNTIFS to SUMIFS and include the sum range right at the start, e.g. for that first formula

=SUM(SUMIFS(DATA!$A$2:$H$A20000,DATA!$H$2:$H$20000,">="&$D$3,DATA!$H$2:$H$20000,"<="&$E$3,DATA!$C$2:$C$20000,'NB Info'!$B7,DATA!$N$2:$N$20000,{"New Business","Transfer Business","Rollover Business"},DATA!$P2:$P20000,"N"))-K7

That will sum column A where all the other criteria are satisfied

 

[RetroMe]

Perfect! Thanks Barry as ever.