Count formula needed

Buster Davis

Trying to do a count formula that compare. So

two columns, One called actual and the otheris call budget

I want to do a count if Actual is Greater than budget then I want to do another count where Budget is greater than actual

Thank you

Amber Eakin

Hello, @Buster Davis . Someone else might have a more elegant method, but I'd recommend a helper column that you can hide after you set it up with a column formula.

In the Helper column, you would use this formula: =IF(Budget@row > Actual@row, "Budget", "Actual")

You would change Budget@row and Actual@row to the names of your Budget and Actual columns, but keep the @row part. Set that to a column formula (right click, "Convert to Column Formula").

Then, for your count, you would use this formula to count the instances where the budget is higher than the actual: =COUNTIF(Helper:Helper, "Budget")

You would use this formula to count the instances where the actual is higher than the budget: =COUNTIF(Helper:Helper, "Actual")

Below is how it looks, but again you could hide the Helper column once it's all set up.

I hope this helps!

DKazatsky

Hi @Buster Davis

Here is one way to accomplish this. Add "helper" column to be used to determine which value is greater. Use this column formula: =IF(Actual@row > Budget@row, "Actual", IF(Budget@row > Actual@row, "Budget")). Note: if the Actual and Budget are equal, this will keep the cell blank.

To determine the over counts, use the following 2 formulas:

Count where Actual is greater =COUNTIF(Greater:Greater, "Actual")

Count where Budget is greater =COUNTIF(Greater:Greater, "Budget")

Hope this helps,

Dave

DKazatsky

Hi @Buster Davis,

The solutions given my myself and @Amber Eakin are almost identical - just be aware of one thing. In Amber's helper column formula, if the budget and actual are equal, it will register as "Actual" whereas in mine it remains blank. Not sure which is best for your use case.

Thanks,

Dave

Buster Davis

for each project, i have a budget column and actual column. I want to do a count to tell me how many are under budget vs over budget. the column names are Actual and budget

Buster Davis

my formula:

=COUNTIFS(Helper:Helper, "Budget")

but I get the error message in the formula column

Amber Eakin

Hi @Buster Davis - You'll need to create a new column called "Helper" next to the Budget and Actual columns and then set it up with a formula like I did in my picture above.