Formula Tweak

Clark Usher

Hi - How to I adjust (add to) this formula so it sums Green and Gray for Clark Usher?

=COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")

Nick Korna

Hi @Clark Usher,

You're halfway there!

If your data range is not going to get any larger then the following formula works:

=COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")+COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Gray").

If you wanted it to be able to take in any future data being added, then remove the numbers in the range so the formula looks at only columns:

=COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Green")+COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Gray").

This is assuming you are only looking at Primary Reviewer - you would need to expand the formula further if also counting secondary reviewer.

Hope this helps, if you have any questions please ask! 😊

Nick Korna

Hi @Clark Usher,

You're halfway there!

If your data range is not going to get any larger then the following formula works:

=COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")+COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Gray").

If you wanted it to be able to take in any future data being added, then remove the numbers in the range so the formula looks at only columns:

=COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Green")+COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Gray").

This is assuming you are only looking at Primary Reviewer - you would need to expand the formula further if also counting secondary reviewer.

Hope this helps, if you have any questions please ask! 😊

Clark Usher

Awesome, thought I was close, it was the + sign to add the nested countifs I was unsure of.

Also, thanks for the further explanation on the data range!

Cheers Clark