sumif? countif?

[Aaron Hodson \(Coversure\)]

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

 

[Pete_UK]

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

 

[Aaron Hodson \(Coversure\)]

Works perfectly!

Thank you very much for your help.

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete