Prev: Map one more column map within a single XSD element.
Next: i want excel to show the first tab upon opening, not the secon
From: Jas on 30 Jan 2010 08:13
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?

From: JLatham on 30 Jan 2010 08:34
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?
>
From: Jas on 30 Jan 2010 09:49
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?
> >
From: JLatham on 30 Jan 2010 14:58
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?
> > >
 | 
Pages: 1
Prev: Map one more column map within a single XSD element.
Next: i want excel to show the first tab upon opening, not the secon