#### Count items in cel

```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?

```
 0
mark1551 (3)
1/10/2005 11:51:26 AM
excel 39879 articles. 2 followers.

7 Replies
408 Views

Similar Articles

[PageSpeed] 1

```=(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 wrote:

> 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?
>
>
>

```
 0
1/10/2005 12:20:24 PM
```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?

On Mon, 10 Jan 2005 07:20:24 -0500, "Jerry W. Lewis"

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

```
 0
mark1551 (3)
1/10/2005 1:51:10 PM
```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.

Mark Coutinho wrote:
>
> 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?
>
> On Mon, 10 Jan 2005 07:20:24 -0500, "Jerry W. Lewis"
>
> >=LEN(SUBSTITUTE(A1,"1s",""))-LEN(SUBSTITUTE(SUBSTITUTE(A1,"1s",""),"s",""))

--

Dave Peterson
```
 0
ec357201 (5290)
1/10/2005 10:06:29 PM
```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!
==============================================

"Mark Coutinho" <mark@soaaids.nl> wrote in message
news:3t15u0l4hpmc6c4i51kh2lc2fmjlkpbsau@4ax.com...
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?

On Mon, 10 Jan 2005 07:20:24 -0500, "Jerry W. Lewis"

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

```
 0
ragdyer1 (4060)
1/11/2005 1:40:47 AM
```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!

On Mon, 10 Jan 2005 17:40:47 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>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.

```
 0
mark1551 (3)
1/11/2005 10:16:32 AM
```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!
==============================================

"Mark Coutinho" <mark@soaaids.nl> wrote in message
news:pe97u0tdpm07t5u73t6p4832c85912o2ni@4ax.com...
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!

On Mon, 10 Jan 2005 17:40:47 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>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.

```
 0
ragdyer1 (4060)
1/11/2005 4:07:04 PM
```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 !
--------------------------------------------------------------------

"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:%23cB3Ze\$9EHA.2076@TK2MSFTNGP15.phx.gbl...
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!
==============================================

"Mark Coutinho" <mark@soaaids.nl> wrote in message
news:pe97u0tdpm07t5u73t6p4832c85912o2ni@4ax.com...
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!

On Mon, 10 Jan 2005 17:40:47 -0800, "RagDyer" <ragdyer@cutoutmsn.com>
wrote:

>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.

```
 0
ragdyer1 (4060)
1/11/2005 4:15:03 PM

Similar Artilces:

Counting Number Records per Unique ID, saving it to another table:
I have a form frmVendors with a subform frmPayments. The main form shows information on vendors, and the subform is a continuous form which lists the checks paid out to each vendor. Users are typically entering new payments into the subform on the main form. The forms are linked to tables: Linked by VendorID (one to many) tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor City tblPayments: Key=CheckID, VendorID, PaymentAmount There is a field in tblVendor called NumberChecks to show the total amount of checks paid out to that vendor. There is a text box on th...

Help - RMS 2.0 Installation Problem
Hello, I just installed RMS 2.0, and the"Add Item" button does not work in the Purchase Order. I click on it, and nothing happens. I have downloaded RMS 2.0 Service Pack #2, but have not installed it yet. Thanks, Greg That problem is addressed in one of the older hot fixes prior to SP2. Marc "gregfrgusa" <gregfrgusa@discussions.microsoft.com> wrote in message news:EEC0B25E-695E-4154-B770-398280F40F08@microsoft.com... > Hello, > > I just installed RMS 2.0, and the"Add Item" button does not work in the > Purchase Order. I click on it, an...

Counting 12-28-07
I have a data element in a table named Results. This is defined as a number field. The Values go from 0 to 11. The problem is I need to count a certain value but sometimes that value has not been chosen and that, of course, returns nothing. I would like it to return the result value I am looking for and a '0'. Any ideas? Post your SQL. -- KARL DEWEY Build a little - Test a little "Bunky" wrote: > I have a data element in a table named Results. This is defined as a number > field. The Values go from 0 to 11. > The problem is I need to count a certai...

Item numbers on purchase orders should be vendor specific as to w.
Right now in Great Plains V8, on the PO setup screen there is a radio button to the user to set whether to use company item numbers or vendor item numbers on purchase order. This option should be moved to be vendor specific. Most of our clients have vendors that do use the same item number as the company and there are only a few vendors who insist on seeing their item numbers on the PO. The only way to accomodate this is to make this option vendor specific. Rich, What you want is easily achieved, as the PO contains all 4 fields: Item Number and Description, and Vendor Item Number and Des...

scan the same item more than once
It possible to scan the same item more than once without creating a new line? =?Utf-8?B?RURXSU4=?= <EDWIN@discussions.microsoft.com> wrote in news:10DB4D57-4770-4947-B45A-B41A7B9B7D5D@microsoft.com: > It possible to scan the same item more than once without creating a new > line? > > > No, when you scan an item it is just going to be added to the next line. You can set the quantity BEFORE you can the item using the "*" key... 4 * <scan barcode ot enter item lookup code> The above sequence will set the quantity to 4... You can also set the...

Applying a tax rate to multiple items at once
Is this possible? I'm certain it would be and I'm overlooking something. No matter what options I choose from within 'Inventory Wizard' I end up having to adjust each item seperately. Not to mention you can't just double click an item in the list, you have to select one, then click on 'Properties' Thank you in advance for any help :) You would need to use an SQL query for this. It is very easy to get yourself in a bind if you don't know what you're doing. Your best bet is to contact your RMS reseller and ask them to do it for you - it should only ...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

How do I empty the "Deleted Items" folder of a personal folder?
Some email goes straight into personal folders, because of the Rules I have set up. When I read the mail in a personal folder, and then delete the item, it shows up in the Deleted Items of that particular personal folder. How do I quickly empty the Deleted Items of a personal folder? Thanks. Right click it and choose "Empty Folder". -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----...

Duplicate Schudules Items in Outlook after changing orginal meeting
When I change a scheduled meeting I sometimes end up with the old item as well as my new meeting in my calendar. What version of Outlook are you using? Specifically what steps are you using to change the meeting? Are you the organizer of the meeting? It only happens sometimes? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm "mikef" <mikef@lionbridge.com> wrote in message news:#8CiMn#HIHA.4808@TK2MSFTNGP05.phx.gbl... > When I change a scheduled meeting I sometimes end up with the old it...

Using Mid Function with Pivot Table Calculated Item
Hi, I'm using excel 2007, I have a pivot table and I'm trying to create a calculated item, the formula for the Calculated item is: IF(MID('Project Title',7,1)="S", .20, .10) The Project Title field contains text and if the 7th character of the project title field is "S" I want the calculated item to return .20 otherwise ..10, but for some reason it is always returning .10. Am I doing something wrong here? Thanks. Simon ...

OWA doesnt display inbox items when clicked on.
I work for a private school, so money isn't flowing endlessly for out IT department. Having said that, we are still using exchange 5.5 (quite comfotably and securely actually). The version of OWA with 5.5 works great for about 95% of my faculty members. However, for a few, they can login to OWA fine and see thier inbox items fine, but when they click on those items to read them, a box appears on screen for a split second, then disappears. It is almost as if their built in pop-up blocker is running, but even disabling that doesnt fix this problem. Any suggestions? thanks in advance jazz ...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

CListCtrl inserting an item
Hi I have my list control being populated and nicely working from my scrollbar and page up/down buttons. The problem I have is, that when I click an item it gets erased. I had thought that maybe I could retrieve the LV_ITEM information, but I am not totally sure how to retrieve my LV_ITEM struct from my list control. I have tried doing a hit test and getting the information with GetItem(LV_ITEM*) from the acquired item number, but this doesnt get populated in any way. Any help guys? Secondly, is it possible to use the LV_ITEM lParam member to store an address of data, so that it can be...

Count Outlook messages.
Hi there I'm pretty new at Outlook 2000 so i'm not too sure weather you can do this or not but i'll try to explain it as best as i can. I'm getting messages from a specific email sender address. And what I want to do is count how many messages i am getting from this sender. I am on a network and i think we are running Windows 2000 server with Exchange 2000. I have a Win2k PC with Outlook 2000. Is there a way to count the amount of messages that i'm getting from the sender or do i need a specific type of software to do it? Thanks guys. Tell me if you need anymore ...

MSCRM item limit?
Do MSCRM have an upperlimit for items in objects like accounts, products, incidents? In the System Customization -> Auto Numbering, you can choose a prefix followed by five digits (XXX-000000). Does this mean that there only can be 99999 items per object or what happens when the counter hits XXX-99999 an you add one item more ? -Thomas Rasmussen There is no limit to the number of rows in any of the tables. For the tables that use the auto-numering, this may mean you need to modify the prefix once you run out of numbers, though I haven't experienced this yet myself. Matt On 2 Mar 2...

Pivot tables- 0 counts don't display
How can I get a 0 counts to display in the pivot chart and subsequent graphs. Thanks to Debra for answering my last question, it was a big help! You could enter a dummy record for each combination in the table, and leave the door field blank. For example: door floor core doortype 1 1 1 2 1 1 3 1 1 These records will be displayed in the column and row area, but won't be counted in the data area, where 'Count of Door' is used. Jim wrote: > How can I get a 0 counts to display in the pivot chart > and subs...

Anyone ever seen a case where FormatConditions.Count = -1 ?
I have a case where I have a selected range (Application.Selection) that I want to do some conditional formatting on and I am seeing a case where Application.Selection.FormatConditions.Count is -1 instead of a value between 0 and 3. Anyone ever seen this before? I am sure that some sort of error condition exists, but don't know the significance of Count = -1 except that I cannot add a condition. I believe the selection is valid. Any help would be appreciated. TIA. Roy You'll see that if the selection contains cells with different conditional formatting. roybrew@att.net wrote:...

How to customize the Item Properties Form
Is there any way to add additional fields to the Item Properties page in RMS? I have used QSRules to modify the POS Screen in the past but don't see any way to modify any of the forms from within Manager. I would like to either add more fields or pop a custom form after tabbing off the Item Lookup Code field. Thanks! Nope, no way to modify the built in forms. You can replace them, as several existing add-ins do. You can also use the ItemSave hook to pop your form after the OK button on Item Properties is clicked. That's the most granular activation that's available. Gle...

Arrange deleted items by date deleted in Outlook 2002
Is there any way to set up a field in the "Deleted Items" folder that will allow you to sort by the date deleted? Thanks. DP My bad. Outlook 2000, not 2002. DP >-----Original Message----- >Is there any way to set up a field in the "Deleted Items" >folder that will allow you to sort by the date deleted? >Thanks. > >DP Um - the deleted items folder is for trash. Why do you keep trash beyond the end of your Outlook session? If you want to retain the mails prior to delete, create a folder for questionable items and move the mail there. Please realize...

Multiple results with Count
Hello Everybody, In Access 2003, I am trying to get multiple results in a query using Count with the same column and table as my source of data. So far I have been unsuccessful in bringing two results of Count onto a same query. For example: I have a list of amounts paid to different employees in a column. I want to count the rows base on two different criteria (i) rows with amounts more than \$1000 each and (ii) rows with amounts less than \$500. I want to get the results of these two criteria in same query but in two different fields with distinct names. What would be the structure of ...

Multiple Item query
I've read a few posts on this query and it looks like creating a table where I input the item numbers in it and linking it to the query seems to be a good way to get a lot of items in a query. Now what If I have hundreds of numbers I want to look up? do I input all 100 numbers into the table? Or what if it's like 10 numbers but I want create seperate queries for different groups of numbers, should I have 1 table for one group of numbers? What would be the easiest way for a novice to do this? It is easier to fill the table with just the values you need for the query you want to run, t...

Sales Report That Includes Items Not Sold
Has anyone seen or created a sales report (grouped by item - one line per item) with total sales, qty sold, quantity on order and the primary supplier's reorder #? I need the result to include all items in the item list even if they weren't sold within the user selected date sold range. We are trying to create a single report that uses sales history, current inventory position (on hand and on order) to better manage the inventory but we need to see all items not just items that have sold. This is a multi-part message in MIME format. ------=_NextPart_000_002B_01CA435E.AEBED810 C...

returning items on purchse orders
Allow return on shippment/invoices on purchse orders that opens the PO up again to allow for it to have a shippment/invoice done again. This would be helpfull if an error is made when the original transaction is done. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. ht...

can the Purging of deleted items be prevented
I have multiple users that like to purge the items that are in the exchage server from outlook without thinking whethere they will need them in the future. My question is, Is there a way to configure or prevent the purge from outlook? Donald Miami, FL Do you have deleted items retention enabled on the server (and is that what they are purging)? You may be able to use group policy to remove the Recover deleted item menu from outlook to prevent them from using it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual ...

How void receipt/shipment with wrong Item account codes
Series : Purchase Order Processing http://img341.imageshack.us/img341/135/20100312185625.png No sure by create a Purchase Return can help =96 my concern is wrong account codes & the standard cost. Under Item maintenance, the item (V0910) has already with wrong account codes and wrong standard cost, Has been wrong even BEFOFE the creation of the receipt shipment. So, the Shipment transaction is now with the wrong accounts. I am wondering if by doing a purchase return (Transaction > Purchasing > Return Transaction Entry) can help to reversing all the wrong entries f...