Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Using CONCATENATE (and a COUNT?) for a total result
I am trying to get a formula to work but says invalid each time. I am at a
loss. In Column C, I am using the formula =CONCATENATE((COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2"))," = Total Items"). In Column B is the object (reward points for this example) I want to count against Column C. In Column A, how do I get the result? Example: Item 1 = 20 points Item 2 = 50 points I have reward points for every Item I sell. Result, how many reward points do I get against all Items sold? So, 6 Item 1s equals 720 points (6x20=720). How to formulate this please? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Using CONCATENATE (and a COUNT?) for a total result
Your formula actually works for me, but it does have one unneeded set of
parenthesis, I rewrote it as: =CONCATENATE(COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2")," = Total Items") and it works also. As for totals for the award points, use SUMIF. In its simple form for your example: =SUMIF(A1:A10,"Item 1",B1:B10) would give the total of points from column B where "Item 1" is in column A. To add words, you can use CONCATENATE or not: =CONCATENATE("Item 1 Points = ", SUMIF(A1:A10,"Item 1",B1:B10)) Hope this gives you a leg up. "Jas" wrote: I am trying to get a formula to work but says invalid each time. I am at a loss. In Column C, I am using the formula =CONCATENATE((COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2"))," = Total Items"). In Column B is the object (reward points for this example) I want to count against Column C. In Column A, how do I get the result? Example: Item 1 = 20 points Item 2 = 50 points I have reward points for every Item I sell. Result, how many reward points do I get against all Items sold? So, 6 Item 1s equals 720 points (6x20=720). How to formulate this please? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Using CONCATENATE (and a COUNT?) for a total result
Thanks for the mistake with () though the SUMIF part doesn't seem to want to
work. Here's my simplified layout. Titling: Column A - Points Earned. Column B - Point Value (how many points for each column C item sold) Column C - Product name (many rows for products) Formula used: Column A - ? (Total points earned for selling product based on point value is not working) Column B - 20 (points for instance) Column C - =CONCATENATE(COUNTIF('Data Sheet'!G4:G50,"Green teeth*(new)")," = Teeth") Column A is where the result displays. Example, 6 green teeth (from column C formula) * 20 points (column B) = 180 (displayed in Column A). "JLatham" wrote: Your formula actually works for me, but it does have one unneeded set of parenthesis, I rewrote it as: =CONCATENATE(COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2")," = Total Items") and it works also. As for totals for the award points, use SUMIF. In its simple form for your example: =SUMIF(A1:A10,"Item 1",B1:B10) would give the total of points from column B where "Item 1" is in column A. To add words, you can use CONCATENATE or not: =CONCATENATE("Item 1 Points = ", SUMIF(A1:A10,"Item 1",B1:B10)) Hope this gives you a leg up. "Jas" wrote: I am trying to get a formula to work but says invalid each time. I am at a loss. In Column C, I am using the formula =CONCATENATE((COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2"))," = Total Items"). In Column B is the object (reward points for this example) I want to count against Column C. In Column A, how do I get the result? Example: Item 1 = 20 points Item 2 = 50 points I have reward points for every Item I sell. Result, how many reward points do I get against all Items sold? So, 6 Item 1s equals 720 points (6x20=720). How to formulate this please? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Using CONCATENATE (and a COUNT?) for a total result
All of the matching type functions such as SUMIF, COUNTIF, V/HLookup all
depend on the spelling and punctuation of things being exactly the same. While Excel will match "item 1" with "Item 1", it won't match " Item 1" to "Item 1", so I'd be checking my spelling and any blank characters before or after any of the entries. ALSO in your Green Teeth example, write the COUNTIF portion with an = symbol right in front of Green, as COUNTIF('Data Sheet'!G4:G50,"=Green teeth*") and see what you get. I put these entries on a sheet: A B 1 Green Teeth Sets 20 2 Green Teeth 10 3 Green Teeth Sets (new) 5 Formula =COUNTIF(A$1:A$10,"=Green Teeth*") & " Green Teeth" gives me "3 Green Teeth" (using the & symbol is much like using CONCATENATE) formula =COUNTIF(A$1:A$10,"=Green Teeth*(new)") & " Green Teeth" gives me "1 Green Teeth" while =SUMIF(A$1:A$10,"=Green Teeth*(new)",B$1:B$10) gives me 5, and =SUMIF(A$1:A$10,"=Green Teeth*",B$1:B$10) returns result of 35. "Jas" wrote: Thanks for the mistake with () though the SUMIF part doesn't seem to want to work. Here's my simplified layout. Titling: Column A - Points Earned. Column B - Point Value (how many points for each column C item sold) Column C - Product name (many rows for products) Formula used: Column A - ? (Total points earned for selling product based on point value is not working) Column B - 20 (points for instance) Column C - =CONCATENATE(COUNTIF('Data Sheet'!G4:G50,"Green teeth*(new)")," = Teeth") Column A is where the result displays. Example, 6 green teeth (from column C formula) * 20 points (column B) = 180 (displayed in Column A). "JLatham" wrote: Your formula actually works for me, but it does have one unneeded set of parenthesis, I rewrote it as: =CONCATENATE(COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2")," = Total Items") and it works also. As for totals for the award points, use SUMIF. In its simple form for your example: =SUMIF(A1:A10,"Item 1",B1:B10) would give the total of points from column B where "Item 1" is in column A. To add words, you can use CONCATENATE or not: =CONCATENATE("Item 1 Points = ", SUMIF(A1:A10,"Item 1",B1:B10)) Hope this gives you a leg up. "Jas" wrote: I am trying to get a formula to work but says invalid each time. I am at a loss. In Column C, I am using the formula =CONCATENATE((COUNTIF(A1:A10,"Item 1")+COUNTIF(A1:A10,"Item 2"))," = Total Items"). In Column B is the object (reward points for this example) I want to count against Column C. In Column A, how do I get the result? Example: Item 1 = 20 points Item 2 = 50 points I have reward points for every Item I sell. Result, how many reward points do I get against all Items sold? So, 6 Item 1s equals 720 points (6x20=720). How to formulate this please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed to concatenate text with result from calculation | Excel Discussion (Misc queries) | |||
Why does Concatenate formula show but not produce a cell result? | Excel Worksheet Functions | |||
How to concatenate 3064 and 01 with 306401 as a result? | Excel Worksheet Functions | |||
calculate count on 2006 total, 2005 total, etc... | Excel Worksheet Functions | |||
UDF to evaluate result of concatenate() with additional arg. | Excel Discussion (Misc queries) |