Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing mulitple line items within date ranges | Excel Worksheet Functions | |||
syntax for countif when cells fall within a numerical range | Excel Worksheet Functions | |||
Help...Summing Items Scattered in a list | Excel Discussion (Misc queries) | |||
Selective summing of table items? | Excel Worksheet Functions | |||
Conditional Summing (Sumif? Countif?) | Excel Worksheet Functions |