COUNTIF - Summing non-numerical items in cells

[Vic]

I have a business in which I need to sum the number of deliveries received on
the various days of each month. Sometimes item X is delivered and sometimes
item Y. To sum the deliveries of item X received in, say, January, I use the
formula =COUNTIF(A1:A31,"*X*") [where cells A1 to A31 cover the 31 days of
the month] and, for item Y, =COUNTIF(A1:A31,"*Y*"). If I receive deliveries
of both X and Y on the same day there is no problem – I can enter both X and
Y in a cell and those two formulas will still work. However, occasionally I
receive two deliveries per day of item X. But if I enter two X’s in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

 

[Mike H]

Vic,

Try this with the item you counting in B1

=SUMPRODUCT((LEN(A1:A31))-(LEN(SUBSTITUTE(UPPER(A1:A31),UPPER(B1),""))))/LEN(B1)

Mike

 

[Mike H]

Hi,

On reflection this may be more complicated than my formula will cope with.
If for example you have "Widgets" in b1 (no quotes) then the formula works
fine if you have several entries in column A like

A box of widgets
A pallet of widgets

But it won't work for a search of 2 different items. Lets wait and see if
someone solves this multi item search with a formula.

Mike

Vic,

Try this with the item you counting in B1

=SUMPRODUCT((LEN(A1:A31))-(LEN(SUBSTITUTE(UPPER(A1:A31),UPPER(B1),""))))/LEN(B1)

Mike

I have a business in which I need to sum the number of deliveries received on
the various days of each month. Sometimes item X is delivered and sometimes
item Y. To sum the deliveries of item X received in, say, January, I use the
formula =COUNTIF(A1:A31,"*X*") [where cells A1 to A31 cover the 31 days of
the month] and, for item Y, =COUNTIF(A1:A31,"*Y*"). If I receive deliveries
of both X and Y on the same day there is no problem – I can enter both X and
Y in a cell and those two formulas will still work. However, occasionally I
receive two deliveries per day of item X. But if I enter two X’s in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

 

[Mark]

I don't know if there is a better way but i would suggest this, using your
example:

=COUNTIF(A38:A53,"*X*")+COUNTIF(A38:A53,"*XX*")

it is simple, simpleminded?, but it does work.

 

[xlmate]

Hi Vic

would you provide an example on this and your result?
How do you enter 2 Xs in the same cell?


--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis
(e-mail address removed)

 

[Ashish Mathur]

Hi,

In a spare column (say column D), use the following array formula
(Ctrl+Shift+Enter)

=IF(LEFT(D7)="X",COUNTA(MID(D7,ROW(INDIRECT("1:"&LEN(D7))),1)),0)

In another spare column (say column E), use the following array formula
(Ctrl+Shift+Enter)

=IF(LEFT(D7)="Y",COUNTA(MID(D7,ROW(INDIRECT("1:"&LEN(D7))),1)),0)

Now you can sum up column D for X's and column E for Y;s

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com