How to use COUNTIFS to check partial contents of a cell & also multiple criteria in range

[TnD_Guy]

Hey friends! Long time no talk to. Anyway, here's what I'm trying to do. I've attached a workbook.

Looking at my test workbook, there is a column which has data like "Top", "Bottom", "Side" or "Front". Well, how can I tell COUNTIFS to look for "to" (which should hopefully return results for "Top" and "Bottom", since they both have "to" in them). I feel like this could done using the asterisk to tell it to look for *+to+*, so basically the "to" with any characters before or after it. I'm just not sure exactly how to structure it.

Also, I know I could use 2 separate functions for this next thing, but what if I wanted to know how to count if a cell range contains totally unrelated data? Like, what if I wanted to know if column B (in my example) contains "B" or "A"? Like, COUNTIFS(B:B, "B" or "A", D:D, "Top") would tell me how many times we have Top in the D column and also A or B in the B column? I know I could do

Please Login or Register  to view this content.

and that would work, but is there a way to consolidate it so I don't have to add two different COUNTIFS?

[XLent]

so

=COUNTIFS(D:D,"*to*")

and, re: 2nd question

=SUM(COUNTIFS(D:D,"Top",A:A,{"A","B"}))

note: you're still performing two separate COUNTIFs but, c/o the array constant and outer sum, the syntax is a little more succinct

[Limor_OP]

Hi
In G3:
SUMPRODUCT(($B$2:$B$21="A")*(($D$2:$D$21="TOP")+($D$2:$D$21="BOTTOM")))
G7:
=SUMPRODUCT((($B$2:$B$21="C")+($B$2:$B$21="A"))*(($D$2:$D$21="FRONT")))

G9:
=SUMPRODUCT((($B$2:$B$21="B")+($B$2:$B$21="A"))*(($D$2:$D$21="SIDE")+($D$2:$D$21="TOP")))

[AliGW]

Maybe:

=SUM(COUNTIFS(D:D,{"Top","Bottom"},B:B,"A"))

=SUM(COUNTIFS(B:B,{"A","C"},D:D,"Front"))

=SUM(COUNTIFS(B:B,{"A","B"},D:D,{"Side","Top"}))

All need to be entered using CTRL+SHIFT+ENTER.

[Glenn Kennedy]

One correction.

If there are 2 formula arrays the {} in th emiddle of the formula, the second one needs to be ; separated not , separated.

=SUM(COUNTIFS(D:D, {"Side","Top"}, B:B, {"A";"B"}))

It remains an array formula.

[AliGW]

Thanks, Glenn - oversight on my part.

[Glenn Kennedy]

See comparison, here.

[TMS]

I like this as it's more flexible:

Please Login or Register  to view this content.

[TnD_Guy]

The array stuff is sorta confusing me. I know what an array is. I've done a bit of programming so I'm not lost on the concept of arrays, but I'll mess with it a little bit and see if I can make some sense of it. Thanks!

[AliGW]

The array stuff means what I said when I said this:

All need to be entered using CTRL+SHIFT+ENTER.

[Glenn Kennedy]

To be honest, I'd ultimately go for a more generic approach, myself.

I'd set up 2 sets of drop down boxes to make the selection of task and location and then use this formula to give me the answer. It is still an array formula:

=SUM(COUNTIFS(B:B,G1:I1,D:D,TRANSPOSE(G2:I2)))

[TnD_Guy]

I like this as it's more flexible:

Please Login or Register  to view this content.

Why is the SUM thing necessary? I thought COUNTIFS automatically added together the number of cells in the range that meet the criteria?

Furthermore, when I use the curly brackets, I'm reading that I must do Ctrl+Shift+Enter. When I do this, Excel slaps Curly brackets on my whole things, even outside the = sign. Is this right?

[Glenn Kennedy]

OK. Because you are doing a countifs with OR criteria, you need to wrap it in a SUM to get a total. Otherwise it'll return only one of them.

The curly brackest inside the array are formula arrays. They give effect (in this case ) to the OR ciriteria. In older (pre Office 365) Excel versions any formula that required recalculations (first count for "Top" and then count for "bottom" are array formulae, and will nto work with enter alone.. They need to be set with CTRL-SHIFT-ENTER. See my standard explanation about array formula:

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[TnD_Guy]

Thanks Mr. Glenn! It seems to be working, and I think I understand it a lot better now.

[Glenn Kennedy]

Just in case you missed it... Take a look at post 11. I think it's the most user friendly approach.

[TnD_Guy]

Just in case you missed it... Take a look at post 11. I think it's the most user friendly approach.

I just grabbed it and I'll take a look. But can you help me understand what's wrong with this formula, and why I'm getting an error?

Please Login or Register  to view this content.

I did the Ctrl+Shift+Enter thing and I got a VALUE error here. The two criteria (A:A, "M") and (B1:B5315 looking at that array) seem to work when I do them as individual COUNTIF formulas. But when I combine them in the COUNTIFS, I get the VALUE error.

[TnD_Guy]

Just in case you missed it... Take a look at post 11. I think it's the most user friendly approach.

I agree. It's definitely user friendly! If I was showing this data to others, I'd want something like this. I might try to incorporate this method. In my real book, there are more than 2 categories that must be counted, but I think I can extrapolate what you've done here into as many categories as needed.

The only reason it might not work is because I really want a side-by-side comparison tool where we can see all of the numbers lined up next to each other, rather than just selecting one set of criteria to see one product.

I need to learn more about that TRANSPOSE function! I've never seen it before and I have no clue what it does, but I'm sure there is plenty of material out there to explain it.

[Glenn Kennedy]

If you mock up a sheet showing what you really really want (that might make a good song??), I will have a go at doing it.

[TnD_Guy]

Can you take a look at the concerns in #16? If I could figure out why I'm getting this VALUE error, I think I'd have it all set.

[AliGW]

The thing that jumps out at me is that the two arrays are different sizes:

=SUM(COUNTIFS(A:A, "M", $B$1:$B$5315, {"*Oth*","*know*",""}))

Try this:

=SUM(COUNTIFS($A$1:$A$5315, "M", $B$1:$B$5315, {"*Oth*","*know*",""}))

[Glenn Kennedy]

I didn't see post 16!! Yes Ali's explanation is probably correct. Did it work?

[CARACALLA]

J3=sumproduct((isnumber(match(d2:d21,d3:d4,0)))*(b2:b21=b2))

j7=sumproduct((isnumber(match(b2:b21,b14:b15,0)))*(d2:d21=d6))

j9=sumproduct((isnumber(match(b2:b21,b3:b4,0)))*(isnumber(match(d2:d21,d16:d17,0))))

[TnD_Guy]

I didn't see post 16!! Yes Ali's explanation is probably correct. Did it work?

Yes, it was the solution! YAY

Lemme ask one final question:

What CAN'T you guys do?

Thanks so much to all!

[Glenn Kennedy]

Well, I can't use VBA.... or turn the clock back 20 years...