#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 46
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 46
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CountIF or SumIF Scott@CW Excel Discussion (Misc queries) 3 December 15th 06 01:54 PM
Countif/Sumif Cain Excel Worksheet Functions 0 February 12th 06 08:35 PM
COUNTIF?? SUMIF?? Vegs Excel Discussion (Misc queries) 1 December 19th 05 04:52 PM
Countif and sumif Visual Excel Discussion (Misc queries) 19 August 10th 05 05:59 PM
{} SumIf and CountIf SS Excel Worksheet Functions 5 May 24th 05 09:19 PM


All times are GMT +1. The time now is 11:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"