Multiple Count IF Function question

[Richmangaskin]

Hi guys, ive been a regular user and viewer of this site but only just joined since i now have a question that i cant find an answer for!! Hopefully someone will be able to help.

My problem is this;
i want to return a count of rows that match 3 criterias.
On Sheet 1 I have two dates in cells B2 and C2.
On sheet 2 in column A I have a range of dates.
On sheet 2 in column M I have a simple Yes or No or blank
On sheet 2 in column V i have an amount of money.

What I want to be able to do is enter a formula which will tell me if there is an entry on Sheet 2 which lies between my two dates in sheet 1, Has a Yes in column M and a figure in Column V......

I know its a long shot but is there any chance?

 

[Richmangaskin]

I would also like to sum the total from Column V of all the rows that match that criteria!

 

[MrKowz]

Try:

Excel 2003 or older:
=SUMPRODUCT(--(Sheet2!A1:A100>=B2),--(Sheet2!A1:A100<=C2),--(Sheet2!M1:M100="Yes"),Sheet2!V1:V100)

Excel 2007 or newer:
=SUMIFS(Sheet2!V:V,Sheet2!A:A,">=B2",Sheet2!A:A,"<=C2",Sheet2!M:M,"Yes")

 

[Richmangaskin]

Hi MrKowz! Thanks for your response. Ive entered the formula and it has returned a 0 which doesnt seem right. It should return a 1 as there is a line in there which has a date between the two dates specified, a yes in column M and an amount of money in V......

 

[MrKowz]

Can you please provide a small sample of data?

 

[MrKowz]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">from</td><td style=";">to</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">9/1/2010</td><td style="text-align: right;;">9/30/2010</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Potential Error?</td><td style="text-align: right;background-color: #FFFF00;;">25</td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">'Northern Trust'!A2:A4>=B2</font>),--(<font color="Red">'Northern Trust'!A2:A4<=C2</font>),--(<font color="Red">'Northern Trust'!B2:B4="Yes"</font>),'Northern Trust'!C2:C4</font>)</td></tr></tbody></table></td></tr></table><br />
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Our Error</td><td style=";">Cost</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">4/1/2010</td><td style=";">Yes</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">9/5/2010</td><td style=";">Yes</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12/31/2010</td><td style=";">No</td><td style="text-align: right;;">5</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Northern Trust</b></th></tr></td></thead></table><br /><br />