Using "COUNTIF" with more than 1 criteria

T

TISSMOD

I am familiar with the formula to count the number of occurrences of a
certain item i.e.:

=COUNTIF(Support!$B:$B,"bl-565*")

Which would filter out the number of products called "bl-565" for
example.

Say, for example if I then want to further filter the number of
bl-565's by problem type, and normally problem type is found by using:

=COUNTIF(Support!$G:$G,A22)-1

Can I combine the two formulas using some kind of logic statement?
i.e.:

=COUNTIF ((Support!$B:$B,"bl-565*") AND (Support!$G:$G,A22)-1)

I imagine this is possible, it's just a matter of getting syntax right
but so far I have been unsuccessful.
 
G

gailann

You could insert another column to concatenate the information in the 2
criteria columns, and then apply the countif function to the new
column.

For example,
column H would have the formula
=B1&G1 (or =concatenate(B1,G1) if you like the long version)

your countif function would be
=countif(Support!$H:$H,"bl-565*")
 
T

TISSMOD

Thanks, but I am not able to add any more columns to the worksheet, onl
extract data from it(v.important), so I need to filter one column the
by another different column.

example:
column 1 - list of colours
column 2 - list of flowers

count number of red roses
count number of red tulips
count number of blue roses
count number of blue tulips

Please help, I am stumped given the complicated nature of my formulas
 
K

Kevin M

Hey TISSMOD, if you're filtering why don't you use autofilter? First filter
by Color, then apply a second filter by flower.

HTH,
Kevin M
 
B

Bob Phillips

Try

=SUMPRODUCT(--(LEFT(Support!$B1:$B100,6)="bl-565"),--(Support!$G1:$G100,A22)
)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
T

TISSMOD

Still not got it I'm afraid.

Can't use pivot tables, because I want to automatically produce data on
a seperate worksheet which will be updated on a graph.

Also, I need to be able to search entire columns, as they will be
updated on a daily basis

I just want to:

Sort by 1 criteria, then by another, and then count the number of
results.

By combining these two specific criteria:

First one: =COUNTIF(Support!$B:$B,"bl-565*")

Second one: =COUNTIF(Support!$G:$G,A22)-1

Tricky, isn't it?
 
P

Pete

What Bob meant is that you can't have $B:$B in the SUMPRODUCT formula.
However, you could have:

=SUMPRODUCT(--(LEFT(Support!$B1:$B65522,6)="bl-565"),
--(Support!$G1:$G65522,A22­))

which will cover almost the complete range of rows. I can't believe you
would need to go to 65535, and 65522 is easy to remember !

Hope this helps.

Pete
 
B

Boulder257

Bob said:
Try

=SUMPRODUCT(--(LEFT(Support!$B1:$B100,6)="bl-565"),--(Support!$G1:$G100,A22)
)

Note that SUMPRODUCT doesn't work with complete columns, you have to
specify
a range.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
Click to expand...
[/QUOTE]


I too need to use a similar function, but I don't understand the
response that was written above. Can you give me a little more
description on how to write this formula so that I can achieve the same
result?
 
T

TISSMOD

Many thanks Bob and Boulder! your advice helped solve my problem!

Drinks are on me whenever you are in Liverpool!
 
B

Bob Phillips

What is your requirement? We can tailor the answer to the need.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)



I too need to use a similar function, but I don't understand the
response that was written above. Can you give me a little more
description on how to write this formula so that I can achieve the same
result?
[/QUOTE]
 
B

Boulder257

My need matches exactly with the one described previously; however, I am
having trouble understanding how to read the formula.

Here is my problem:

In column A I have a Product order (Coke, Pepsi, Dr. Pepper)
In Column B I have the cost of that specific order

I need to total how many dollars spent on Coke Orders...

I hope I explained that right and I can't even tell you how much I
appreciate your help!
 
B

Boulder257

Actually, here is more how it works

Column A = Product (Coke, Pepsi, Dr. Pepper)
Column B = Whether purchased (y or n)
Column C = cost of order

I need to select Coke orders, which have been purchased , and total the
cost.
 
B

Bob Phillips

Okay, you don't need the LEFT as you want to test the whole word, whereas
the other guy wanted to pick up all starting with.

You need

=SUMPRODUCT(--(A2:A200="Coke"),--(B2:B200="y"),C2:C200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top