How do I Write a Count if formula?
In a different sheet I want to write a formula that counts how many CDO is reliant and have a status of 5?
and a formula that counts how many CDO is AHN with a status of 5?
Thanks
Best Answer

This is how your first formula should look
Answers

Hello,
Plug each formula in and that will give you a summary for the sheet. Be sure to click "reference another sheet" in order to reference the columns.
=COUNTIFS(CDO:CDO, "AHN", Status:Status, 5)
=COUNTIFS(CDO:CDO, "Reliant", Status:Status, 5)

It didn't work it, it say's Unparseable

If you are putting these formulas in another sheet then you will need to use cross sheet references.
Here are some instructions on how to create those:

@Jeff M. @Paul Newcome I tried to reference it but no luck

=COUNTIFS({CDO}:{CDO},"AHN",{Status}:{Status},5)
I did this formula with cross sheet reference did I do it wrong?

Make sure you click here when entering the columns in the formula.
Then click the column name to select the entire column and hit insert reference at the bottom.

@Jeff M. =COUNTIFS({CDO}, "AHN", {Status:Status}, 5) Is it like this? I did the insert reference you mentioned still no luck?
=COUNTIFS({CDO}, "AHN", {Status:Status}, 5) is this right?

@Jeff M. And in the one you had above I don't see where you referenced CDO in your formula?

In the new sheet, create your 2 columns for summary. In the first cell, in the first column, type =COUNTIFS( Now click the "reference another sheet" in the little window that pops up and then browse your sheets and select the one you want to reference.
Once you have highlighted the column and click the insert reference button at the bottom of the screen, it should update your formula with the reference name. It should look like this but with your sheet name instead of "test sheet" =COUNTIFS({Test Sheet Range 1},
If you don't need this in another sheet, you can alternatively select the summary option in the menu that's on the right of the master sheet. Create a text/number column, then plug in the original formula without needing to reference the sheet. Then another for the next summary

This is how your first formula should look

@Jeff M. Thank you so much it worked!
Help Article Resources
Categories
Check out the Formula Handbook template!