Microsoft Excel 2003 (full product)

Hi, To simplify this, I have published a [modified] copy of the GDocs spreadie and shown a bunch of the formulae here:

https://spreadsheets.google.com/pub?key=0AmsJVtMcG2QwdGpaUkMwX2VURFA1TUhvZGk3dDNEaUE&single=true&gid=0&output=htmlWhat I'm trying to do is to count the number of cases that each person has in that spreadie. I've already got formulae to show the number of batteries that I have to ship out, and other formulae that aren't relevant to this issue, but I want to get it to show the number of individual cases.

For example, I have a formula to show how many batteries are required for each case (Column D, formula shown in Column F) but that doesn't take any notice of who owns the case.

I would like to get it to be able to reference a cell to pull the engineer, rather than specifying each engineer explicitly - similar to how I have done with D72 (formula shown in E72) but that is just counting the number of times the engineer's name is listed in Column B, and each row represents one battery that needs to be shipped.

I've tried a few variations of COUNTIF statements, and I am fairly certain that it requires an AND statement, but I can't get my brain to work out the formula.

All help gladly accepted.

Nude_Lewd_Man

✔ Best Answer

What I want to get to do is have a formula that will automagically calculate and show that I ("N_L_M" in the demo) have 10 cases, which can then be dragged down (to fill) to show that "AN" has one case alongside their 'name' and that "Other" has one case...I take that to be a

YesHere is an

Arrayformula that should do what you want:Someplace along Row 70 ( I have it at F70 on my spreadsheet)

enter the formula:=SUM(($B$4:$B$61=B70)*($D$4:$D$61<>""))

It is an ARRAY formula so:

ENTERING AN ARRAY FORMULA:

To enter a formula as an array formula:First type the formula in the cell

DO NOTpress EnterYou

MUSTpress the CTRL SHIFT and ENTER keys at the same time.You must do this the first time you enter the formula and whenever you edit the formula later.

If you have done this properly, Excel will display the formula enclosed in curly braces { } like:{=SUM(($B$4:$B$61=B70)*($D$4:$D$61<>""))}

You do not type in the braces -- Excel will display them automatically.

If you fail to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

Once you have it entered correctly then just drag it down to get the totals for AN & OTHER.MIKE

Have you tried a =SUMIF() ? On your Google sheet, starting at cell E80, (your customer column)

insert a list of all your customers and in cell F80 enter the formula, like:E F 80) BB 708 < Formula 81) Bup 1806 82) CL 1035 83) EH 142 84) GR 1296 85) HW 1584 86) JH 246 87) Me 1086 88) PU 2460 89) WG-N 2148 90) WG-QC2 936The formula is:

~~=SUMIF($E$6:$E$65,E80,$C$6:$C$65)~~Edit, range was incorrect:

=SUMIF($E$4:$E$61,E80,$C$4:$C$61)

Drag down and you have all your totals.MIKE

Hi, It doesn't look like that is going to do what I'm after - it would (on looking at it) be working against the customer, rather than the "Owner" column.....I'm also a little intrigued as there aren't 80 rows in there... :confused:

For example, using that same spreadie (the 'original' it was taken from has been updated somewhat since I set this version up) in cell C66 the idea is to count the number of different/individual cases that are owned by an engineer - so in this case it should show that N_L_M has 10 cases. (Again, just to clarify, it should be counting against the reference cell with the engineer's name in, rather than specificly naming them.)

Bear in mind that as each battery is to have a row for itself, another engineer may have another case (for a different system/rack) that is for the same customer - so a simple count of different customers wouldn't really be the best option...

N_L_M

To make it easier to get to the GDocs spreadie, here's the link as a hyperlink:

I'm a bit dense today,

You have a Owner Heading, a Customer Heading and your talking aboutowned by an engineer

What I'm trying to do is to count the number of cases that each person has in that spreadie.What

personare you talking about? Owner, Customer .......Sitting here I have no idea.

For example, I have a formula to show how many batteries are required for each caseI get that part.

MIKE

Sorry to use so much boldto distinguish the comment and my response, but just want to make it easy to tell - as I don't think that <quote> and </quote> would work.. :)You have a Owner Heading, a Customer Heading and your talking about owned by an engineer

The owner is the engineer who owns the case, hence why in the example, "N_L_M" would have 10 cases.What I'm trying to do is to count the number of cases that each person has in that spreadie.

What person are you talking about? Owner, Customer .......See above. I want to be able to see (quickly) how many cases each engineer 'owns' - not how many cases are for each customer..Sitting here I have no idea.

I appreciate that, hence why I set up a demo spreadie to enable me to explain it better. Looking back, I prolly could have hidden the "Customer" column and moved any cells over, to save any confusion - but I kinda thought that my description would have made it clear enough.. :)

If I understand you correctly, Engineer N_L_M has 55 cases, and you want a break down of the number of cases, IE 10

Correct?

MIKE

To save confusion, I used N_L_M as shorthand for Nude_Lewd_Man (so you can just say that I have.......) as I'm the one who has been ~~@r$~~bothered to be proactive about this...As per the 'demo' spreadie linked above, I have 55 batteries to ship out, with a total of 10 cases. As it happens, I have combined most of the cases that I have for one customer into one case - but other engineers may not do the same, meaning that there could be multiple cases for one customer, but for different systems.

As I thought I'd mentioned earlier, each row represents one battery - as some racks in the same system will have different expiry dates. (As it turns out, those columns were hidden, but that doesn't really matter for this)

The "Batteries" column shows how many batteries are required for each case (listed against the first instance of the case number) which is then tallied up at the bottom of the 'main' section.

Basically, trying to think of a better way of describing what I'm getting at would be, if I was to concatenate column B and column C (in a different column, with "=B4&C4") then I would want to be able to count the number of different combinations there are, and how many of those contained the user it is referencing - in most of these cases it would be looking for "N_L_M".

I hope that makes sense and clears up any confusion... :o)

At the time I created that demo, there were a total of 12 cases, 10 of which were mine. (The live version now has more than this, but we're working on the 'demo' version) What I want to get to do is have a formula that will automagically calculate and show that I ("N_L_M" in the demo) have 10 cases, which can then be dragged down (to fill) to show that "AN" has one case alongside their 'name' and that "Other" has one case... Should "AN" or "Other" then add another case to the list, or if a case was transitioned from one engineer (Owner) to another, then this should be updated with the formula/e...

N_L_M

What I want to get to do is have a formula that will automagically calculate and show that I ("N_L_M" in the demo) have 10 cases, which can then be dragged down (to fill) to show that "AN" has one case alongside their 'name' and that "Other" has one case...I take that to be a

YesHere is an

Arrayformula that should do what you want:Someplace along Row 70 ( I have it at F70 on my spreadsheet)

enter the formula:=SUM(($B$4:$B$61=B70)*($D$4:$D$61<>""))

It is an ARRAY formula so:

ENTERING AN ARRAY FORMULA:

To enter a formula as an array formula:First type the formula in the cell

DO NOTpress EnterYou

MUSTpress the CTRL SHIFT and ENTER keys at the same time.You must do this the first time you enter the formula and whenever you edit the formula later.

If you have done this properly, Excel will display the formula enclosed in curly braces { } like:{=SUM(($B$4:$B$61=B70)*($D$4:$D$61<>""))}

You do not type in the braces -- Excel will display them automatically.

If you fail to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.

Once you have it entered correctly then just drag it down to get the totals for AN & OTHER.MIKE

Hi, Thanks for that, and while I don't understand what an 'array formula' is or how it works, it does...

I have adjusted it, and GDocs also amended it after I'd entered it - it now looks like this:

=ArrayFormula(SUM(($B$3:$B$62=$B66)*($D$3:$D$62<>"")))That is for the standalone cell that I was using to test, and it works perfectly for all engineers. Copied down to alongside the other cells and it still worked, took it to the 'live' version of the spreadie and -after further adjusting to reflect cells- it is still working...

Oh, in case of any confusion, I include the row above and the row below the data section so that it doesn't break the calculations if/as/when it gets sorted. ;op

Thank you very much! :oD

N_L_M

Dear All, If i have data from (A1..A30) and i want to find the same values in the whole column spouse i want to know that which values same to calculate that 45 is 4 time ,55 as 3 time just like this

Please start a new thread with your question, using a subject line specific your question. DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History