COUNTIF - Summing non-numerical items in cells

V

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?
 
M

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
 
M

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

Mike H said:
Vic,

Try this with the item you counting in B1

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

Mike

Vic said:
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?
 
M

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.
 
X

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)
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top