Count IFS and Count IF

Susan Swisher

I have a formula that is counting, for a specific criteria (see below), in a specific column; however, now I need it to look in a 2nd column and also include it in the COUNT; however, I do not want it to be "double-COUNTED". For example, if the amount will always appear in Column 1 and then eventually appears in Column 2 (the end result). SO, I need it to look in Column 1 (include in COUNT) and then look in Column 2 (COUNT). The amounts could change, we are counting a range of $$:

·      $0.00M (10)

·      $0.00M-$0.50M (13)

·      $0.51-$0.99M (13)

·      $1.00M-$1.49M (12)

·      $1.50M-$4.00M (12)

$1.00M-$1.49M

=COUNTIFS([Approved/Contracted Budget $$ (Total)]1:[Approved/Contracted Budget $$ (Total)]76, ">1000000", [Approved/Contracted Budget $$ (Total)]1:[Approved/Contracted Budget $$ (Total)]76, "<1500000")

The Formula above shows Column 2, the FINAL number will appear; the 1st column it starts out in is called "Budget Requested (Name)"

Julie Fortney

Could you provide a screenshot of the referenced columns? (blocking out sensitive data)

Genevieve P.

Hi @Susan Swisher

I agree that screen captures would be helpful - are your values numbers, or are they text strings that say "$0.00M-$0.50M"?

The current COUNTIFS formula is looking for text of ">1000000" not a number greater than 1,000,000

If you're looking for a number, you'll need to remove out the quotes like so:

=COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], >1000000, [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], <1500000)

For your structure with the two columns, I assume that FIRST you want to count the second column value amounts, and only if there is no second column value then we count the first column to complete the rest of the counts. Is that correct?

If so, we can add two different COUNTIFS statements together:

=COUNTIFS(count of Column 2) + COUNTIFS(count of Column 1 where Column 2 has no value)

Try something like this:

=COUNTIFS([Budget $$ Second Column]:[Budget $$ Second Column], >1000000, [Budget $$ Second Column]:[Budget $$ Second Column], <1500000) + COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], >1000000, [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], <1500000, [Budget $$ Second Column]:[Budget $$ Second Column], "")

The "" at the end mean that the second column has to be blank for the count to look into your first column. If this hasn't worked, let me know what formula you tried and what your column names are (and how your data is stored!) I'm certain we can help you get to your end goal. 🙂

Cheers,

Genevieve

Susan Swisher

So, it is working; however, it is counting too many; it is giving me a total of 10; when column 2 is only 1 and column 1 is 2; for a total of 3. See the formula I used:

=COUNTIFS([Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], >1000000, [Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], <15000000) + COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], >1000000, [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], <1500000, [Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], "")

Susan Swisher

Screenshot of sheet

Susan Swisher

It should technically only be 2; because in the 3rd ROW, both the Approved/Contracted (Column) and the Budget Requested (Column) is completed. It should always look in the Approved/Contracted (Column) 1st; if that is filled OUT, only COUNT that number and DO NOT count the Budget Requested (Column). But it needs to look in the 2nd Column IF Column 1 is blank to see if it needs to COUNT that number if it matches the criteria.

Susan Swisher

Well, figured it out (on my own), I had too many "0"s in one of the numbers. Now everything works, as expected! Thank you!

Genevieve P.

Hey @Susan Swisher

Apologies for the delay, but I'm so glad to hear you sorted it out! Thanks for coming back to us with your resolution. 🙂

Susan Swisher

@Genevieve P. .. additional problem; my counts are double-counting, in some cases; I think it has to do with numbers that are slightly above a certain threshold; but I can not (for the life of me), figure it out. It is driving me nuts. Below is a screen shot of the categories; so, you'd need formulas (which I have) less than and greater than numbers.

Susan Swisher

this is my formula for the $0M to $0.5M (and it is counting an additional 3, and I know that is incorrect:

=COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], >500000, [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], <1000000) + COUNTIFS([Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], >500000, [Budget Requested (INCYTE)]:[Budget Requested (INCYTE)], <1000000, [Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], @cell = "", Approved:Approved, 1)

Susan Swisher

this is the series of numbers, in this sheet, I only have 40 entries; yet my sheet summary is counting 43 studies

Genevieve P.

Hey @Susan Swisher

This formula looks to be the one for 0.51m - 0.99m

I'm going to make it easier to follow by simplifying the column names:

=COUNTIFS([Total]:[Total], >500000, [Total]:[Total], <1000000) + COUNTIFS([Requested]:[Requested], >500000, [Requested]:[Requested], <1000000, [Total]:[Total], @cell = "", Approved:Approved, 1)

Notice that it says this must be greater than 500,000 but less than 1,000,000

There are no = signs here, so if a row is 500,000 exactly, then it would not be counted. Are you perhaps wanting to count exactly from that number, or exactly to another number? If so, you'd need an = sign as well:

=COUNTIFS([Total]:[Total], >=500000, [Total]:[Total], <1000000) + COUNTIFS([Requested]:[Requested], >=500000, [Requested]:[Requested], <1000000, [Total]:[Total], @cell = "", Approved:Approved, 1)

Or do you have the = sign in the 0 - 0.5?

=COUNTIFS([Total]:[Total], >0, [Total]:[Total], <=500000) + COUNTIFS([Requested]:[Requested], >0, [Requested]:[Requested], <=500000, [Total]:[Total], @cell = "", Approved:Approved, 1)

Keep in mind that your second COUNTIF is filtering out any rows that have data in the Green column - it has to be a blank cell. This means that if there's a $0 in the Green column that row will not be counted in your second COUNTIF - is that what you're looking to do?

Susan Swisher

@Genevieve P. one more question, please

my formula:

=COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], =0, Approved:Approved, 1)

NOW, I need to add another condition: (if another [COLUMN] = "something" ONLY count those items

AND a second formula, to state, count if it DOESN'T equal that "something"

Simple enough?

Genevieve P.

Hey @Susan Swisher

Yes, no problem!

=COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], 0, Approved:Approved, 1, [COLUMN]:[COLUMN], "Something")

and

=COUNTIFS([Approved/Contracted Budget $$ (Total)]:[Approved/Contracted Budget $$ (Total)], 0, Approved:Approved, 1, [COLUMN]:[COLUMN], <> "Something")

Cheers,

Genevieve

Susan Swisher

@Genevieve P. along with this formula, if my end result is a large number $22,682,243 and I need to round to the M and get $22.7 (M) what do I ADD to the end of the formula? to achieve that?

Genevieve P.

Hey @Susan Swisher

This sounds like a new formula referencing a result of another formula, is that correct?

Here's a different Community post that may be helpful in this instance. You'll need to do the math to get the decimal place you want then add the "M" on as text:

https://community.smartsheet.com/discussion/comment/404969#Comment_404969