Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with criteria | Excel Worksheet Functions | |||
countif criteria sg | Excel Worksheet Functions | |||
Countif with 2 criteria | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |