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.


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile: http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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*")


--
gailann
------------------------------------------------------------------------
gailann's Profile: http://www.excelforum.com/member.php...o&userid=30798
View this thread: http://www.excelforum.com/showthread...hreadid=504931

Thanks, but I am not able to add any more columns to the worksheet, only
extract data from it(v.important), so I need to filter one column then
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!


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile: http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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

"TISSMOD" wrote:


Thanks, but I am not able to add any more columns to the worksheet, only
extract data from it(v.important), so I need to filter one column then
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!


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile: http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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)

"TISSMOD" wrote in
message ...

Thanks, but I am not able to add any more columns to the worksheet, only
extract data from it(v.important), so I need to filter one column then
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!


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile:
http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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?


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile: http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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

Bob Phillips Wrote:
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)


[/color]


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?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile: http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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

Drinks are on me whenever you are in Liverpool!


--
TISSMOD
------------------------------------------------------------------------
TISSMOD's Profile: http://www.excelforum.com/member.php...o&userid=30830
View this thread: http://www.excelforum.com/showthread...hreadid=504931

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Boulder257" wrote
in message ...

Bob Phillips Wrote:
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)





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?


--
Boulder257
------------------------------------------------------------------------
Boulder257's Profile:[/color]
http://www.excelforum.com/member.php...o&userid=20053
View this thread: http://www.excelforum.com/showthread...hreadid=504931