NEED TO COUNTIF WITH TWO CRITERIA
 

Hi
I am trying to count only if both criteria are met I do not have countifs
available and also tried to use sumproduct which does not seem to work
either...
heres what I need to do
first range = B16-B1000 (contains different Status such as Won Job)
Second range =C16:C1000 (contains months)

I need to count numbers of each status for example Won Job for Jan..all way
through to Dec for the different statuses that I have

Jan Feb March
At Tender
Client Not Interested
Competitor Appointed
Letter Sent
Project On Hold
Quoted
Won Job
Total New Leads


I have set the calculations at the top of the worksheet to look like above
--

Kind Regards
Michelle

NEED TO COUNTIF WITH TWO CRITERIA
 

This sounds like a good reason to learn about pivottables.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

CAYLEY01 wrote:

Hi
I am trying to count only if both criteria are met I do not have countifs
available and also tried to use sumproduct which does not seem to work
either...
heres what I need to do
first range = B16-B1000 (contains different Status such as Won Job)
Second range =C16:C1000 (contains months)

I need to count numbers of each status for example Won Job for Jan..all way
through to Dec for the different statuses that I have

Jan Feb March
At Tender
Client Not Interested
Competitor Appointed
Letter Sent
Project On Hold
Quoted
Won Job
Total New Leads

I have set the calculations at the top of the worksheet to look like above
--

Kind Regards
Michelle

--

Dave Peterson

NEED TO COUNTIF WITH TWO CRITERIA
 

Try
=SUMPRODUCT(--(Sheet1!$B$16:$B$1000=$A2),--(Sheet1!$C$16:$C$1000=B$1))
and copy across and then down...
Assuming you have a range setup like you have shown below (Labels down rows
from A2 and across columns from B1) and your data is in Sheet1 in the range
Sheet1!B16:C1000

"CAYLEY01" wrote:

Hi
I am trying to count only if both criteria are met I do not have countifs
available and also tried to use sumproduct which does not seem to work
either...
heres what I need to do
first range = B16-B1000 (contains different Status such as Won Job)
Second range =C16:C1000 (contains months)

I need to count numbers of each status for example Won Job for Jan..all way
through to Dec for the different statuses that I have

Jan Feb March
At Tender
Client Not Interested
Competitor Appointed
Letter Sent
Project On Hold
Quoted
Won Job
Total New Leads


I have set the calculations at the top of the worksheet to look like above
--

Kind Regards
Michelle

NEED TO COUNTIF WITH TWO CRITERIA
 

Thanks very much for your reply I had got most of the way there but had not
entered the sheet name so your reply has helped me finish off and get the
formula working
--
Kind Regards
Michelle


"Sheeloo" wrote:

Try
=SUMPRODUCT(--(Sheet1!$B$16:$B$1000=$A2),--(Sheet1!$C$16:$C$1000=B$1))
and copy across and then down...
Assuming you have a range setup like you have shown below (Labels down rows
from A2 and across columns from B1) and your data is in Sheet1 in the range
Sheet1!B16:C1000

"CAYLEY01" wrote:

Hi
I am trying to count only if both criteria are met I do not have countifs
available and also tried to use sumproduct which does not seem to work
either...
heres what I need to do
first range = B16-B1000 (contains different Status such as Won Job)
Second range =C16:C1000 (contains months)

I need to count numbers of each status for example Won Job for Jan..all way
through to Dec for the different statuses that I have

Jan Feb March
At Tender
Client Not Interested
Competitor Appointed
Letter Sent
Project On Hold
Quoted
Won Job
Total New Leads


I have set the calculations at the top of the worksheet to look like above
--

Kind Regards
Michelle