COUNTIFS AND SUM OF CRITERIAS

[Pietro Di Micio]

Hello Folks,

My challenge:
1) I have items on column "A" and when I find the name I have the state in column "B" BUT when I find this COUNTIFS I need to count 3 of 5 regions, in other words, count a mutiple criteria inside the COUNTIFS.

2) I did it by adding a lot of COUNTIFS+COUNTIFS+ etc... but I do believe that maybe there is something more clean tho show the same result.

3) I checked out in other posts but did not find something similar.

Someone could help me on this formula?

Tks

 

[Joe4]

Check out the "Count cells with OR Conditions in Excel" section of this link: Excel COUNTIF and COUNTIFS with OR conditions
It shows you multiple different ways of doing it.

 

[jasonb75]

Something like

=SUM(COUNTIFS(B:B,{"region1","region2"}))

You can add as many items as needed in the array, or you can use a range of cells for the criteria.

 

[Pietro Di Micio]

Something like

=SUM(COUNTIFS(B:B,{"region1","region2"}))

You can add as many items as needed in the array, or you can use a range of cells for the criteria.

I am trying this but without success:

COUNTIFS(C$44:$C$1000;"Yasmim Lee";$M$44:$M$1000;"OP";SUM(COUNTIFS($G$44:$G$1000;"Booked/Won"; "Committed"; "Probable")))

Because I have to conclude the first step and then sum ("Booked/Won"; "Committed"; "Probable"); I mean, I need to sum only this criterias inside Yasmim Lee and OP

Any suggestion?

 

[jasonb75]

You're missing { and } from the formula, also it all needs to be in one COUNTIFS function,

SUM(COUNTIFS(C$44:$C$1000;"Yasmim Lee";$M$44:$M$1000;"OP";$G$44:$G$1000;{"Booked/Won"; "Committed"; "Probable"})))

 

[Pietro Di Micio]

You're missing { and } from the formula, also it all needs to be in one COUNTIFS function,

SUM(COUNTIFS(C$44:$C$1000;"Yasmim Lee";$M$44:$M$1000;"OP";$G$44:$G$1000;{"Booked/Won"; "Committed"; "Probable"})))

Great! worked! Thanks a lot

 

[Pietro Di Micio]

You're missing { and } from the formula, also it all needs to be in one COUNTIFS function,

SUM(COUNTIFS(C$44:$C$1000;"Yasmim Lee";$M$44:$M$1000;"OP";$G$44:$G$1000;{"Booked/Won"; "Committed"; "Probable"})))

Just one more question... If I need to add to this formula above a SUM of another Column; how can I do this? I mean, sum off all numbers realted to Booked...Commited and Probable...

Tks

 

[jasonb75]

Did you check the link that @Joe4 provided for you earlier?

 

[Pietro Di Micio]

Did you check the link that @Joe4 provided for you earlier?

Yeap! but to be really honest I did no find something similar to my question OR I do not have the correct knowledge to concatenate the ideas/formulas.

 

[jasonb75]

Something like this to SUM column A based on the same criteria.

=SUM(SUMIFS($A$44:$A$1000,$C$44:$C$1000;"Yasmim Lee";$M$44:$M$1000;"OP";$G$44:$G$1000;{"Booked/Won"; "Committed"; "Probable"})))