Using IF, AND, OR to create a summary function

jmo

Hi team!

I'm trying to create a summary report that counts across 2 columns with various criterion the return a count.

Column 1 (Current status): drop down options - No impact; 75% capacity available; 50% capacity available; 25% capacity available; 0% available

Column 2 (Risk to Company): drop down options - Low risk; Med risk; High risk

So I need a formula that counts Med risk & High risk (column 2) plus has a current status (column 1) of "No impacts".

I can figure out the other counts after that first "status" is calculated.

I struggle with combining COUNTIFS with AND/OR functions.

Any help would be greatly appreciated.

Paul Newcome

Try something like this to exclude "Low Risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], @cell <> "Low Risk")

or this to specify "Med risk" or "High risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], OR(@cell = "Med Risk", @cell = "High Risk"))

Paul Newcome

Yes. @cell just tells the formula to evaluate the range on a cell by cell basis.

Paul Newcome

Try something like this to exclude "Low Risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], @cell <> "Low Risk")

or this to specify "Med risk" or "High risk"...

=COUNTIFS([Current Status]:[Current Status], "No Impact", [Risk to Company]:[Risk to Company], OR(@cell = "Med Risk", @cell = "High Risk"))

jmo

@Paul Newcome - does @cell work in a Summary Report?

Paul Newcome

Yes. @cell just tells the formula to evaluate the range on a cell by cell basis.

jmo

Thanks @Paul Newcome - that worked like a charm!

Paul Newcome

Happy to help! 👍️

Thai La

This post helped me out, thanks guys!

I was trying to report back if a column had either yellow or red status and the formula @cell worked for me.

Thanks,

-Thai

Paul Newcome

@Thai La That's great! 👍️

It is always nice to know that multiple people are able to benefit from a single solution.