• 460K Members
• 11.9K Online
• 557K Conversations
SOLVED

## Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

Occasional Contributor

# Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

I am trying to figure out if a region/section in the column is blank or not. It's in order by region but each region can be listed multiple times, so, for example, SEC36 is on the list 30 times, but only 14 of them have a plan listed. The plan is listed in Column E. I don't need to know which type of plan, I just need to know if they have one or not.

=COUNTIFS(B2:B206,"SU036", E2:E206,"<>")

I started with this, and it counts column B, but if it's blank or not.

16 Replies

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

The formula looks correct and should return the count of cells containing "SU036" with non-blank plans.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

The criteria2 argument of COUNTIFS is incomplete. There is no value against which E2 could be compared.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

No value as in a number? I just want it to count if it has text in the box? Maybe I need CountA?

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

That is what I thought it gives the number 14, but when I clear a plan the number does not change.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

@Armanda406 , how do you clean the plan, just delete the value from the cell(s) or by some other way? If that's the formula which returns "" your COUNTIFS won't consider it as the blank.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

@Twifoo wrote:
The criteria2 argument of COUNTIFS is incomplete. There is no value against which E2 could be compared.

A criterion "<>" in any of the IFS functions does carry meaning.

The equivalent condition in SUMPRODUCT, say, would be

= ( value <> "" )

Within an IFS function is suppose it could be presented as

= COUNTIFS( …, …, value, "<>" & "" )

but that reduces to the first form.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

What do you think of ">@" as a test for the existence of text?

It attempts to combine @Armanda406's idea of COUNTA with the IFS formula structure.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

I am trying to copie a formule used in tab sheet 'Sales' to tab sheet 'Total Sales'.

However it doesn't copy the formule but it's just mentioning the tabsheet name + cell number.

Any idea what this could be? @Peter Bartholomew

Best,

Max

Solution

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

@Peter Bartholomew , IMHO the main question here is what we consider as empty cell. That could be the blank cells (no content at all) only, or blank cells and cells with empty string ("", which we usually use to imitate blank cells). If do small test we will see the difference

Here two yellow cells are with empty string and one cell is blank. Total 6 cells, 2 empty plus 1 blank.

COUNTA() doesn't consider cells with empty strings as blank and count them, same do COUNTIFS(). Just in case, COUNTBLANK() in opposite counts both blank and empty ans blanks. SUMPRODUCT() does correct calculations excluding all cells with empty strings and blank ones. Variant with LEN() is more straightforward and gives less reasons for misinterpretations.

As for @ that's not wildcard, only the character which is more close to the beginning of ASCII table compare to other characters. However, # is even earlier plus any number is always less than any text, thus formula counts only the cell with a1.

Back to initial question I'd use SUMPRODUCT like

`=SUMPRODUCT((B2:B206="SU036")*(LEN(E2:E206)>0))`

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

@Maximus1988 , I guess that's the question for separate conversation, better to start such. Plus clarify a bit what do you copy - formula itself or cell with the formula. And you not only copy it, you paste it - just Paste all cell content, or Paste Special -> Formulas or what.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

I am not sure whether this relates to @Armanda406's question.

However, you formula will point to the same cells and the sheet name is needed for references between sheets.  If there are values on the new sheet which could meaningfully be referenced by the formula, then you simply need to work through the references, removing the sheet name and adjusting the position of the reference if necessary.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

Thanks for the summary; there is still a certain amount of trial and error with my formula development in this area.  I selected and have used ">@" on occasion because it comes before 'A' in the ASCII sequence.  Mind you the sort order is not always obvious when it comes to mixes of numbers, letters, non-alphanumeric characters and spaces.

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

Yes, I was just deleting the value from the cell. I was just trying to test it to see if it would update, but I remember now that it will still count it, so if I don't need to clear a plan then it is working. My other part to this, is how do I get the formula to count each change in SU?

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

Hi,

Do I understand correctly what other words you need number of unique SU minus one? Or that could be the situation when you change from SU1 on SU2 and after that back on SU1 and it will be 2 changes?

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

I think I figured it out, the SU036 changes to SU074, or SU131 and I need to know for each SU the number of plans. I think I figured it out, for now. I copied the SU list and then removed any duplicates, I added to Column G and then using absolute and adding the Cell I needed to reference that contains each Unique SU, it seems to work out and count each SU that is not blank.

=SUMPRODUCT((B\$2:B\$206=G2)*(LEN(E\$2:E\$206)>0))

# Re: Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.

@Armanda406 Yes, it works. It could be one more step to select unique SU automatically by formula, but if you have more or less static data that's not quite important.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies