Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif? countif?
Hello,
I need some help with the following: I require cell K3 (in worksheet named 'brokers') to count the number of times the data in A3 matches those in mar!B:B. I have acheived this by using formula: =COUNTIF(mar!B:B,Brokers!A:A) However, i now need to insert another arguement, where in addition to the above, only count if mar!M:M contains the letter 'y'. Please help, Thanks Aaron |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif? countif?
You can only use COUNTIF (and SUMIF) if there is only one condition.
Try this instead: =SUMPRODUCT((mar!B1:B100=Brokers!A3)*(mar!M1:M100= "y")) Note that you can't use full-column references (unless you have XL2007), so adjust the ranges to suit. Hope this helps. Pete On Sep 3, 11:51*am, "Aaron Hodson \(Coversure\)" wrote: Hello, I need some help with the following: I require cell K3 (in worksheet named 'brokers') to count the number of times the data in A3 matches those in mar!B:B. I have acheived this by using formula: =COUNTIF(mar!B:B,Brokers!A:A) However, i now need to insert another arguement, where in addition to the above, only count if mar!M:M contains the letter 'y'. Please help, Thanks Aaron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif? countif?
Works perfectly!
Thank you very much for your help. "Pete_UK" wrote in message ... You can only use COUNTIF (and SUMIF) if there is only one condition. Try this instead: =SUMPRODUCT((mar!B1:B100=Brokers!A3)*(mar!M1:M100= "y")) Note that you can't use full-column references (unless you have XL2007), so adjust the ranges to suit. Hope this helps. Pete On Sep 3, 11:51 am, "Aaron Hodson \(Coversure\)" wrote: Hello, I need some help with the following: I require cell K3 (in worksheet named 'brokers') to count the number of times the data in A3 matches those in mar!B:B. I have acheived this by using formula: =COUNTIF(mar!B:B,Brokers!A:A) However, i now need to insert another arguement, where in addition to the above, only count if mar!M:M contains the letter 'y'. Please help, Thanks Aaron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif? countif?
You're welcome - thanks for feeding back.
Pete On Sep 3, 2:45*pm, "Aaron Hodson \(Coversure\)" wrote: Works perfectly! Thank you very much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF or SumIF | Excel Discussion (Misc queries) | |||
Countif/Sumif | Excel Worksheet Functions | |||
COUNTIF?? SUMIF?? | Excel Discussion (Misc queries) | |||
Countif and sumif | Excel Discussion (Misc queries) | |||
{} SumIf and CountIf | Excel Worksheet Functions |