Solved COUNTIF multiple columns

Microsoft Excel 2003 (full product)
November 11, 2010 at 01:25:32
Specs: Windows 7, N/A
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=html

What 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


See More: COUNTIF multiple columns

Report •


✔ Best Answer
November 11, 2010 at 10:29:17
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 Yes

Here is an Array formula 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 NOT press Enter

You MUST press 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

http://www.skeptic.com/



#1
November 11, 2010 at 07:47:27
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       936

The 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

http://www.skeptic.com/


Report •

#2
November 11, 2010 at 08:02:35
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


Report •

#3
November 11, 2010 at 08:04:38
To make it easier to get to the GDocs spreadie, here's the link as a hyperlink:

https://spreadsheets.google.com/pub?key=0AmsJVtMcG2QwdGpaUkMwX2VURFA1TUhvZGk3dDNEaUE&single=true&gid=0&output=html


Report •

Related Solutions

#4
November 11, 2010 at 09:08:56
I'm a bit dense today,
You have a Owner Heading, a Customer Heading and your talking about owned by an engineer

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

Sitting here I have no idea.

For example, I have a formula to show how many batteries are required for each case

I get that part.

MIKE

http://www.skeptic.com/


Report •

#5
November 11, 2010 at 09:27:19
Sorry to use so much bold to 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.. :)


Report •

#6
November 11, 2010 at 09:49:20
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

http://www.skeptic.com/


Report •

#7
November 11, 2010 at 10:02:09
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)


Report •

#8
November 11, 2010 at 10:06:34
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


Report •

#9
November 11, 2010 at 10:29:17
✔ 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 Yes

Here is an Array formula 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 NOT press Enter

You MUST press 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

http://www.skeptic.com/


Report •

#10
November 11, 2010 at 23:24:04
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


Report •

#11
November 12, 2010 at 05:44:35
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

#12
June 26, 2011 at 00:50:04
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


Report •

#13
June 26, 2011 at 06:25:46
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.


Report •

Ask Question