Count IFS and Count IF
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)"
Answers
-
Could you provide a screenshot of the referenced columns? (blocking out sensitive data)
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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)], "")
-
Screenshot of sheet
-
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.
-
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!
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
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)
-
this is the series of numbers, in this sheet, I only have 40 entries; yet my sheet summary is counting 43 studies
-
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?
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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?
-
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:
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!