COUNTIF - Summing non-numerical items in cells
 

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 Xs in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

COUNTIF - Summing non-numerical items in cells
 

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" wrote:

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 Xs in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

COUNTIF - Summing non-numerical items in cells
 

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" wrote:

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" wrote:

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 Xs in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

COUNTIF - Summing non-numerical items in cells
 

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.

"Vic" wrote:

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 Xs in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

COUNTIF - Summing non-numerical items in cells
 

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










"Vic" wrote:

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 Xs in the same
cell, Excel treats it as only one. How do I get the program to count them
both?

COUNTIF - Summing non-numerical items in cells
 

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:"&L EN(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:"&L EN(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

"Vic" wrote in message
...
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 Xs in the
same
cell, Excel treats it as only one. How do I get the program to count them
both?