Using COUNTIF on multiple fields

R

robotman

I'm trying to use COUNTIF to summarize different conditions with 2
parameters.

Example 2 column table:

Apple 1
Apple 3
Bear 3
Bear 1
Bear 2

For example, how can I get a count of everything that is a Bear AND is
1?
Click to expand...

Sorta like this (but this is the wrong syntax):
=COUNTIF(A1:B5,AND(A1:A5=Bear,B1:B5>1))

Thanks!
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

maybe a longwinded solution

You create a third column whcih is the concatenation of columns 1 and 2
and then apply countif.

=concatenate (A1,B1) will give you "Apple1"

=countif(range, "apple1")

This will temporarily solve your problem till one of the experts give a
better solution.

Cheers
Prasad
 
E

excelexpert1973

Hi

This can be solved by typing this formula

=SUM(IF(A1:A6="b",IF(B1:B6=5,1,0),0))
But make sure that you click CTRL & SHIFT when entering this formula.
These type of formulas are called ARRAY FORMULAS.

This type of formula is discussed at
http://www.mrexcel.com/tip031.shtml

Prasad
 
E

excelexpert1973

Hi

This can be solved by typing this formula

=SUM(IF(A1:A6="b",IF(B1:B6=5,1,0),0))
But make sure that you click CTRL & SHIFT when entering this formula.
These type of formulas are called ARRAY FORMULAS.

This type of formula is discussed at
http://www.mrexcel.com/tip031.shtml

Prasad
 
R

robotman

Hmmm.... still not working.

For the 2nd parameter, I need to use a greater than condition.

= sumproduct((A2:A22="Bear")*(B2:B22>1))

but this returns 0 for some reason. (I did enter as an array)

Then I need a bit fancier where the 2nd parameter is in a range:

= sumproduct((A2:A22="Bear")*(1.5<B2:B22<3.5))

this also reports 0.

Any ideas?

Thanks.
 
R

robotman

Hmmm.... still not working.

For the 2nd parameter, I need to use a greater than condition.

= sumproduct((A2:A22="Bear")*(B2:B22>1))

but this returns 0 for some reason. (I did enter as an array)

Then I need a bit fancier where the 2nd parameter is in a range:

= sumproduct((A2:A22="Bear")*(1.5<B2:B22<3.5))

this also reports 0.

Any ideas?

Thanks.
 
R

robotman

Hmmm.... still not working.

A couple challenges:

1. I need the condtion to be a reference to another cell. For example
If A2:A22=C1 where (C1 = "Bear")

= sumproduct((A2:A22=C1)*(B2:B22>1))
but this returns 0 for some reason. (I did enter as an array)

2. For the 2nd parameter, I need to use range:

= sumproduct((A2:A22=C1)*(1.5<B2:B22<3.5))
this also reports 0.

The key problem seems to setting the first criteria to a reference (C1)
rather than a text value.

Any ideas?


Thanks.


Don said:
use
=sumproduct((a2:a22="Bear")*(b2:b22=1))
Click to expand...

!
 
R

robotman

Hmmm.... still not working.

A couple challenges:

1. I need the condtion to be a reference to another cell. For example
If A2:A22=C1 where (C1 = "Bear")

= sumproduct((A2:A22=C1)*(B2:B22>1))
but this returns 0 for some reason. (I did enter as an array)

2. For the 2nd parameter, I need to use range:

= sumproduct((A2:A22=C1)*(1.5<B2:B22<3.5))
this also reports 0.

The key problem seems to setting the first criteria to a reference (C1)
rather than a text value.

Any ideas?


Thanks.


Don said:
use
=sumproduct((a2:a22="Bear")*(b2:b22=1))
Click to expand...

!
 
G

Guest

Sumproduct does not need to be entered as an array.
Also, each "argument" needs to be separately.

Try:
= sumproduct((A2:A22="Bear")*(B2:B11>1.5)*(B2:B22<3.5))

If you aren't getting the expected results, look at column B, if it is
really formatted as text, sumproduct, in the above expample, will not work
until you convert it to a number. (Data > Text to Columns)
 
G

Guest

Sumproduct does not need to be entered as an array.
Also, each "argument" needs to be separately.

Try:
= sumproduct((A2:A22="Bear")*(B2:B11>1.5)*(B2:B22<3.5))

If you aren't getting the expected results, look at column B, if it is
really formatted as text, sumproduct, in the above expample, will not work
until you convert it to a number. (Data > Text to Columns)
 
G

Guest

sorry, left out part of the formula!

try: =sumproduct((A2:A22="Bear")*(B2:B11>1.5)*(B2:B22<3.5),(B2:B22))
 
G

Guest

sorry, left out part of the formula!

try: =sumproduct((A2:A22="Bear")*(B2:B11>1.5)*(B2:B22<3.5),(B2:B22))
 
D

Don Guillett

The original formula I gave DOES work.
This will work on your MODIFIED REQUEST without the last
=sumproduct((A2:A22="Bear")*(B2:B22>1.5)*(B2:B22<3.5))
or
=SUMPRODUCT(--(F2:F22="Bear"),--(G2:G22>1.5),--(G2:G22<3.5))

I suspect you have to trim spaces from your column with bear
=SUMPRODUCT(--(TRIM(F2:F22)="Bear"),--(G2:G22>1.5),--(G2:G22<3.5))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel function - countif 2
Countif(and 1
Help with function 2
Countif Function Using "AND" 3
COUNTIF Multiple Values 3
countif 2
countif 5
How to Control a formula by a character in adjacent column on same 4

Top