Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
I need to calculate how many same products are chosen in a table from
validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are 870 product items (column A) with their price (column B) from the Data Sheet though commonly only a maximum of 10 should appear in the tally next to the data sold. eg. The data sold input using validation list/combobox (Sales Sheet). A B... K L 11 Blue $50 12 Red $40 13 Blue $50 14 Orange $20 15 Blue $50 16 Orange $20 ... 91 In this example above, there a 3x Blue, 1x Red, 2x Orange. I require the tally list to look like this alphabeticalised by name: ie. The tally list K-L. A B... K L 11 Blue $50 Blue 3 12 Red $40 Orange 2 13 Blue $50 Red 1 14 Orange $20 .. .. 15 Blue $50 16 Orange $20 ... 91 When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no results (0) are returned from the range of data [=COUNTIF(A10:B91,"Blue")]. Is this because of v/data or I'm just doin' it wrong? Also, surely I do not need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same formula? Thanks a lot. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
Countfi should of given the number of times B appeared in the range. You
could of just done column A also not sure why you have [] brackets. =COUNTIF(A10:A91,"Blue") to actualy sum the answers then use Sum if =SUMIF(A10:A91,"Blue",B10:B91) Note: check to see if there are any blank characters in the cells that have Blue. try adding to the worksheet =len(A13) which will return the number of characters at A13. This should return a 4. If it is not 4, then you havve some spaces in your data. To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In the From box type one space. Then replace all. "Asiageek" wrote: I need to calculate how many same products are chosen in a table from validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are 870 product items (column A) with their price (column B) from the Data Sheet though commonly only a maximum of 10 should appear in the tally next to the data sold. eg. The data sold input using validation list/combobox (Sales Sheet). A B... K L 11 Blue $50 12 Red $40 13 Blue $50 14 Orange $20 15 Blue $50 16 Orange $20 .. 91 In this example above, there a 3x Blue, 1x Red, 2x Orange. I require the tally list to look like this alphabeticalised by name: ie. The tally list K-L. A B... K L 11 Blue $50 Blue 3 12 Red $40 Orange 2 13 Blue $50 Red 1 14 Orange $20 .. .. 15 Blue $50 16 Orange $20 .. 91 When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no results (0) are returned from the range of data [=COUNTIF(A10:B91,"Blue")]. Is this because of v/data or I'm just doin' it wrong? Also, surely I do not need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same formula? Thanks a lot. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
yep, that works, had spaces on the end of the data to be counted somehow when
filtering however, what do i do about all those products listed (blue, red...) as there are 870 of them. Blue red etc are just examples of a product. Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with 870 products do not need to be entered into the formula? The name of the product and how many times it appears in the list needs to be displayed in the tally. The name isn't appearing only next to the count in it's own table. "Joel" wrote: Countfi should of given the number of times B appeared in the range. You could of just done column A also not sure why you have [] brackets. =COUNTIF(A10:A91,"Blue") to actualy sum the answers then use Sum if =SUMIF(A10:A91,"Blue",B10:B91) Note: check to see if there are any blank characters in the cells that have Blue. try adding to the worksheet =len(A13) which will return the number of characters at A13. This should return a 4. If it is not 4, then you havve some spaces in your data. To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In the From box type one space. Then replace all. "Asiageek" wrote: I need to calculate how many same products are chosen in a table from validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are 870 product items (column A) with their price (column B) from the Data Sheet though commonly only a maximum of 10 should appear in the tally next to the data sold. eg. The data sold input using validation list/combobox (Sales Sheet). A B... K L 11 Blue $50 12 Red $40 13 Blue $50 14 Orange $20 15 Blue $50 16 Orange $20 .. 91 In this example above, there a 3x Blue, 1x Red, 2x Orange. I require the tally list to look like this alphabeticalised by name: ie. The tally list K-L. A B... K L 11 Blue $50 Blue 3 12 Red $40 Orange 2 13 Blue $50 Red 1 14 Orange $20 .. .. 15 Blue $50 16 Orange $20 .. 91 When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no results (0) are returned from the range of data [=COUNTIF(A10:B91,"Blue")]. Is this because of v/data or I'm just doin' it wrong? Also, surely I do not need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same formula? Thanks a lot. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
You can concatenate text in a formula
=CONCATENATE("Number of Blue entries = ",TEXT(COUNTIF(A10:A91,"Blue"))) "Asiageek" wrote: yep, that works, had spaces on the end of the data to be counted somehow when filtering however, what do i do about all those products listed (blue, red...) as there are 870 of them. Blue red etc are just examples of a product. Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with 870 products do not need to be entered into the formula? The name of the product and how many times it appears in the list needs to be displayed in the tally. The name isn't appearing only next to the count in it's own table. "Joel" wrote: Countfi should of given the number of times B appeared in the range. You could of just done column A also not sure why you have [] brackets. =COUNTIF(A10:A91,"Blue") to actualy sum the answers then use Sum if =SUMIF(A10:A91,"Blue",B10:B91) Note: check to see if there are any blank characters in the cells that have Blue. try adding to the worksheet =len(A13) which will return the number of characters at A13. This should return a 4. If it is not 4, then you havve some spaces in your data. To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In the From box type one space. Then replace all. "Asiageek" wrote: I need to calculate how many same products are chosen in a table from validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are 870 product items (column A) with their price (column B) from the Data Sheet though commonly only a maximum of 10 should appear in the tally next to the data sold. eg. The data sold input using validation list/combobox (Sales Sheet). A B... K L 11 Blue $50 12 Red $40 13 Blue $50 14 Orange $20 15 Blue $50 16 Orange $20 .. 91 In this example above, there a 3x Blue, 1x Red, 2x Orange. I require the tally list to look like this alphabeticalised by name: ie. The tally list K-L. A B... K L 11 Blue $50 Blue 3 12 Red $40 Orange 2 13 Blue $50 Red 1 14 Orange $20 .. .. 15 Blue $50 16 Orange $20 .. 91 When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no results (0) are returned from the range of data [=COUNTIF(A10:B91,"Blue")]. Is this because of v/data or I'm just doin' it wrong? Also, surely I do not need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same formula? Thanks a lot. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COUNTIF - SUMIF don't appear to work in this sheet.
Thanks a lot!
This works just fine. It counts the products (without summing them). Thanks again, cya "Joel" wrote: You can concatenate text in a formula =CONCATENATE("Number of Blue entries = ",TEXT(COUNTIF(A10:A91,"Blue"))) "Asiageek" wrote: yep, that works, had spaces on the end of the data to be counted somehow when filtering however, what do i do about all those products listed (blue, red...) as there are 870 of them. Blue red etc are just examples of a product. Surely something like this: =COUNTIF(A10:A91,"Blue" "red""orange...") with 870 products do not need to be entered into the formula? The name of the product and how many times it appears in the list needs to be displayed in the tally. The name isn't appearing only next to the count in it's own table. "Joel" wrote: Countfi should of given the number of times B appeared in the range. You could of just done column A also not sure why you have [] brackets. =COUNTIF(A10:A91,"Blue") to actualy sum the answers then use Sum if =SUMIF(A10:A91,"Blue",B10:B91) Note: check to see if there are any blank characters in the cells that have Blue. try adding to the worksheet =len(A13) which will return the number of characters at A13. This should return a 4. If it is not 4, then you havve some spaces in your data. To remove spaces highlight A10:a91. the go to the Edit Menu - Replace. In the From box type one space. Then replace all. "Asiageek" wrote: I need to calculate how many same products are chosen in a table from validated data to show a tally (using 2 sheets, 1 v/data & Tally List [Sales Sheet] and 1 v/lookup [Data Sheet]). I've tried SUMIF and COUNTIF but didn't seem to work at all on the v/data sheet unless I stuffed up, but where? How do i calculate how many "Blue" items are listed each time a Blue item is added to the Sales Sheet via combo/lists? Each time a new item is listed, it will automatically appear in the tally list as a new item and/or count tally of appearences sorted by ranking. There are 870 product items (column A) with their price (column B) from the Data Sheet though commonly only a maximum of 10 should appear in the tally next to the data sold. eg. The data sold input using validation list/combobox (Sales Sheet). A B... K L 11 Blue $50 12 Red $40 13 Blue $50 14 Orange $20 15 Blue $50 16 Orange $20 .. 91 In this example above, there a 3x Blue, 1x Red, 2x Orange. I require the tally list to look like this alphabeticalised by name: ie. The tally list K-L. A B... K L 11 Blue $50 Blue 3 12 Red $40 Orange 2 13 Blue $50 Red 1 14 Orange $20 .. .. 15 Blue $50 16 Orange $20 .. 91 When i use SUMIF or COUNTIF as in Excel help with say "blue" as exampled, no results (0) are returned from the range of data [=COUNTIF(A10:B91,"Blue")]. Is this because of v/data or I'm just doin' it wrong? Also, surely I do not need to enter ... "Blue" "Orange" "Red" etc of 870 items into the same formula? Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GETTING SELECTED DATA FROM A WORK SHEET TO OTHER WORK SHEET | Excel Discussion (Misc queries) | |||
Need Countif to work like Sumif | Excel Worksheet Functions | |||
Populating work sheet combox with another work sheet values | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions |