countif multiple conditions

[gerryR]

Hi All

I'm trying to setup a formula which counts the amount of times 2 particular
conditions are true. From what I've read, =countif can only be used for
single conditions. I've tried some other options (see below) but all to no
avail. Below is an eg of what I'm trying to do.

Basically, I have a worksheet (A) with several coulms and 20000 rows.

John word
Mary excel
Ann excel
John word
Mary word
Ann excel

I then have another worksheet (B) with just a list of user names

John
Mary
Ann

What I want to do is count the amount of times each user has used each app.
So the final worksheet (B) would look like

word excel
John 2 0
Mary 1 1
Ann 0 2

As I say, my list of usernames is over 100 and the total rows is over 20000
so manual counting (even sorting my name) would be a chore. I also tried
the below formulae but it doesn't seem to work (found reference from the
web and adapted it to suit my needs but may have made a mistke?)

=SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0))

Apologies for the long post, if any one can point me in the right direction
I'd really appreciate it.
regards
Gerry

 

[Ron Coderre]

I think a Pivot Table would be the least amount of work....

On Sheet_B:
<Data><Pivot Table>
Use: Excel
Select your data from Sheet_A

Click the [Options] button
• For empty cells show: 0

Click the [Layout] button
• ROW: Drag the NAME field here
• COLUMN: Drag the APP field here
• DATA: Drag the APP field here
If it doesn't list as Count of APP...dbl-click it and set it to Count
• Click [OK]

Select where you want the Pivot Table...and you're done.

The end result will look like this:
Count of App App
Name excel word Grand Total
Ann 2 0 2
John 0 2 2
Mary 1 1 2
Grand Total 3 3 6


To refresh the Pivot Table, just right click it and select Refresh Data

If the data range will change regularly, base the Pivot Table on a Dynamic
Range Name, (which will automatically expand and contract to accommodate the
data).
For instructions: http://www.contextures.com/xlNames01.html#Dynamic

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[andy62]

Hi Gerry - There is a nifty function called SUMPRODUCT which can be used to
check any number of conditions and count the number of times all of them are
met. It's not exactly intuitive, but once you get used to it you'll probably
get a lot out of it. In your example, the list of people and tools they used
is in SheetA, starting at A1. In SheetB set up a table with names down the
left and tools across the top. If you started that table in A1 then the
first cell you need to populate is B2, correct? In B2 type (or paste):

=SUMPRODUCT(--(SheetA!$A$1:$A$6=$A2),--(SheetA!$B$1:$B$6=B$1))

Now copy that formula to all the other empty cells in the table. Of course,
if your sheet names or cell locations are different, you'll to adjust the
formula.

You probably want to know how it works, so here is some explanation. Each
factor in SUMPRODUCT is a set of TRUE/FALSE evaluations. So the first factor
tests to see if the names in column A of SheetA are equal to the name to the
far left of the formula in SheetB. For example, the formula I provided above
tests all the names to see which ones are equal to "John". The output from
that par of the function is "True,False,False,True,False,False". The "--" in
front of the factor converts True to "1" and "False" to "0". The second
factor does the same thing, but looks in column B of SheetA for the tools.
Its findings are also converted to 1's and 0's. SUMPRODUCT multiplies all
the pairs together (1X1,0X0,0X0,1X1,0X1,0X0) and then sums all those products.

In case you're not familiar, the $ in front of row or column references
"anchor" those letters/numbers so that when you copy a formula to another
location they don't adjust. In the formula I provided above, you want some
or the references to adjust and others not to. If you were to simply type
the adjusted formula in each cell in the table, you could omit the $.

Hope that helps. Write back if stuck.

 

[gerryR]

Perfect!

Thank you very much!

I think a Pivot Table would be the least amount of work....

On Sheet_B:
<Data><Pivot Table>
Use: Excel
Select your data from Sheet_A

Click the [Options] button
. For empty cells show: 0

Click the [Layout] button
. ROW: Drag the NAME field here
. COLUMN: Drag the APP field here
. DATA: Drag the APP field here
If it doesn't list as Count of APP...dbl-click it and set it to Count
. Click [OK]

Select where you want the Pivot Table...and you're done.

The end result will look like this:
Count of App App
Name excel word Grand Total
Ann 2 0 2
John 0 2 2
Mary 1 1 2
Grand Total 3 3 6


To refresh the Pivot Table, just right click it and select Refresh Data

If the data range will change regularly, base the Pivot Table on a Dynamic
Range Name, (which will automatically expand and contract to accommodate
the
data).
For instructions: http://www.contextures.com/xlNames01.html#Dynamic

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hi All

I'm trying to setup a formula which counts the amount of times 2
particular
conditions are true. From what I've read, =countif can only be used for
single conditions. I've tried some other options (see below) but all to
no
avail. Below is an eg of what I'm trying to do.

Basically, I have a worksheet (A) with several coulms and 20000 rows.

John word
Mary excel
Ann excel
John word
Mary word
Ann excel

I then have another worksheet (B) with just a list of user names

John
Mary
Ann

What I want to do is count the amount of times each user has used each
app.
So the final worksheet (B) would look like

word excel
John 2 0
Mary 1 1
Ann 0 2

As I say, my list of usernames is over 100 and the total rows is over
20000
so manual counting (even sorting my name) would be a chore. I also tried
the below formulae but it doesn't seem to work (found reference from the
web and adapted it to suit my needs but may have made a mistke?)

=SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0))

Apologies for the long post, if any one can point me in the right
direction
I'd really appreciate it.
regards
Gerry

 

[gerryR]

Thanks Andy, I used a pivot table in this instance but I'll definetly save
that one for later!.

rgds
gerry