Count items in cel

[Mark Coutinho]

Okay, here's my problem:

In my excel-list I have 3 codes: "l","s" and "ls".
l is the equivalent of 1,50 euro
s the equivalent of 1 euro
ls is thus 2,50 euro

The codes are entered during the month.
At the end of the month I want to see how much someone has to pay.
So if that's 3 times "l", 2 times "s" and 1 time "ls" that would be 9
euro.

How do I do that in a formula?

Thanks in advance!

 

[Jerry W. Lewis]

=(LEN(A1)-LEN(SUBSTITUTE(A1,"1s","")))/2

gives the number of "1s" values in A1

=LEN(SUBSTITUTE(A1,"1s",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"1s",""),"s",""))

gives the number of "s" values in A1

=LEN(SUBSTITUTE(A1,"1s",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"1s",""),"1",""))

gives the number of "1" values in A1.

If the information spans a range of cells, you could change the single
cell to a range, embend the formula in a SUM function, and array enter
it (Ctrl-Shift-Enter)

Jerry

 

[Mark Coutinho]

Thanks for that, Jerry.

Although your "1" (one) is in fact a "l" (letter L) it should work.

However:
I want to count the values per row (from B4 to AF4 - being all the
days of a month). And for the next row, of course, B5 to CF5 etc.

How would that be?

 

[Dave Peterson]

Are you really counting the number of times "s" appears in one cell or are you
counting the number of cells that contain "s" in that range?

=countif(b4:af4,"ls")

If each cell in b4:af4 only contains the characters ls.

 

[RagDyer]

Still unsure if you need "1S" (one+s) or "LS".

Try this in AG4 or A4:

=SUMPRODUCT((B4:AF4={1;"S";"LS"})*({1.5;1;2.5}))

And drag down to copy as needed.

Now, this *only* works for me (US version) using semi-colons in the array
constants, commas *don't* work.
I'm also using decimals, not commas for the numeric values.

Don't exactly know what punctuation you'll need to make this work for you.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




Thanks for that, Jerry.

Although your "1" (one) is in fact a "l" (letter L) it should work.

However:
I want to count the values per row (from B4 to AF4 - being all the
days of a month). And for the next row, of course, B5 to CF5 etc.

How would that be?

 

[Mark Coutinho]

Ragdy, thanks for your formula, which I translated to (Dutch,
including the comma's instead of decimals)

=SOMPRODUCT((B4:AF4={"L";"S";"LS"})*({1,5;1;2,5}))

I can't figure out why it doesn't work, though. I get a #N/B (english
perhaps #N/A) message.
I thought maybe it was because not all cells were filled, but when I
filled all of them the same message appeared.

If you're not sure what I'm talking about, I'd love to send you the
Excel-sheet in which I'm trying to make this work.
On the other hand: maybe it's just a little adjustment that's needed.

Thanks!

 

[RagDyeR]

This *does* work in my US version:

=SUMPRODUCT((B4:AF4={"L";"S";"LS"})*{1.5;1;2.5})
Empty cells in the range *are* acceptable.


This gives *me* a #N/A error:

=SUMPRODUCT((B4:AF4={"L","S","LS"})*{1.5,1,2.5})
Note the commas used in place of the semi-colons to produce the error.


*HOWEVER*, if I change the range from a row to a column, the commas *do*
work:

=SUMPRODUCT((B4:B54={"L","S","LS"})*{1.5,1,2.5})

SO, try using commas in the array constants for your version,
OR
FWIW
For a test, change the range to a column using the semi-colons, and see what
happens.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Ragdy, thanks for your formula, which I translated to (Dutch,
including the comma's instead of decimals)

=SOMPRODUCT((B4:AF4={"L";"S";"LS"})*({1,5;1;2,5}))

I can't figure out why it doesn't work, though. I get a #N/B (english
perhaps #N/A) message.
I thought maybe it was because not all cells were filled, but when I
filled all of them the same message appeared.

If you're not sure what I'm talking about, I'd love to send you the
Excel-sheet in which I'm trying to make this work.
On the other hand: maybe it's just a little adjustment that's needed.

Thanks!

 

[RagDyeR]

As an afterthought,
Any formula in the range that returns a #N/A error will cause the error to
be returned in the SumProduct() cell also.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

This *does* work in my US version:

=SUMPRODUCT((B4:AF4={"L";"S";"LS"})*{1.5;1;2.5})
Empty cells in the range *are* acceptable.


This gives *me* a #N/A error:

=SUMPRODUCT((B4:AF4={"L","S","LS"})*{1.5,1,2.5})
Note the commas used in place of the semi-colons to produce the error.


*HOWEVER*, if I change the range from a row to a column, the commas *do*
work:

=SUMPRODUCT((B4:B54={"L","S","LS"})*{1.5,1,2.5})

SO, try using commas in the array constants for your version,
OR
FWIW
For a test, change the range to a column using the semi-colons, and see what
happens.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Ragdy, thanks for your formula, which I translated to (Dutch,
including the comma's instead of decimals)

=SOMPRODUCT((B4:AF4={"L";"S";"LS"})*({1,5;1;2,5}))

I can't figure out why it doesn't work, though. I get a #N/B (english
perhaps #N/A) message.
I thought maybe it was because not all cells were filled, but when I
filled all of them the same message appeared.

If you're not sure what I'm talking about, I'd love to send you the
Excel-sheet in which I'm trying to make this work.
On the other hand: maybe it's just a little adjustment that's needed.

Thanks!