How can I count IF, then, else statements that have values?
 

I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

How can I count IF, then, else statements that have values?
 

Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

How can I count IF, then, else statements that have values?
 

Here's something you might play around with:

=A1&B1&"=" & COUNTIF(E$2:E$7,E2)

This formula could be copied down as needed. However, you'll end up with
duplicates. So if you know all of the names ahead of time, then you can
work that into the equation.

Good luck.

"Scoops" wrote in message
oups.com...

Jenny wrote:
I tried Count if statements and sum statements, but nothing is working.
I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then
print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

How can I count IF, then, else statements that have values?
 

I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

How can I count IF, then, else statements that have values?
 

Jenny wrote:
I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

Hi Jenny

A Pivot Table should be able to do the work you want.

Regards

Steve

How can I count IF, then, else statements that have values?
 

Why not do a simple table with names in rows and "animals" in columns and put
SUMPRODUCT statement in cell referencing apporpriate row/column cells and
copy accross down
Dog Cat ... etc
Jane =Sumproduct(....=Jane, .....=Cat)
Jim
Jeff

"Jenny" wrote:

I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

How can I count IF, then, else statements that have values?
 

because part of the data is being imported somewhere else.

"Toppers" wrote:

Why not do a simple table with names in rows and "animals" in columns and put
SUMPRODUCT statement in cell referencing apporpriate row/column cells and
copy accross down
Dog Cat ... etc
Jane =Sumproduct(....=Jane, .....=Cat)
Jim
Jeff

"Jenny" wrote:

I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

How can I count IF, then, else statements that have values?
 

a pivot table won't work

"Scoops" wrote:


Jenny wrote:
I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

Hi Jenny

A Pivot Table should be able to do the work you want.

Regards

Steve

How can I count IF, then, else statements that have values?
 

Jenny wrote:
a pivot table won't work

"Scoops" wrote:


Jenny wrote:
I'm thinking maybe a macro...the perimeters are always going to change and
there is multiple values. There are 4 possible values in the first column
and 7 values in the 2nd, but multiple rows.

"Scoops" wrote:


Jenny wrote:
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

Hi Jenny

If your data is in the range A1:B6, then you could put your parameters
("Jane" and "Dog")
in A8 and B8 and in C8 type:

=SUMPRODUCT((A1:A6=A8)*(B1:B6=B8))

Either change the parameters as you want or put an exhaustive list
(A8:B11 in your example above) and copy the formula down to C11.

Regards

Steve

Hi Jenny

A Pivot Table should be able to do the work you want.

Regards

Steve

Hi Jenny

It seems you haven't given us all the relevant information

A pivot table works fine for me based on the data layout you described
and Toppers' SUMPRODUCT solution is eminently workable too (I was
looking at that before offering pivot table as a built-in solution).

Regards

Steve