Sumproduct another condition

[blossomthe2nd]

Hi there

I am using the below formula to Sum number of accounts in column C when the date in column A is in May.

=SUMPRODUCT((MONTH(A2:A6)=5)*(C2:C6))

I wish to add another condition so If its may and Column B = "YES" then sum Column C,

Can anyone please help ?

Also is there a restriction to the amount of Conditions that excel can use in these type of formulas ?

Thanks
Anne

 

[VoG]

Try

=SUMPRODUCT(--(MONTH(A2:A6)=5),--(B2:B6="YES"),C2:C6)

 

[blossomthe2nd]

Cool Thanks VOG ,

So using this I can add loads more conditions to look like the below

=SUMPRODUCT(--(MONTH(A2:A6)=5),--(B2:B6="Anne"),--(C2:C6="Yes"),D2:D6)

 

[ayazgreat]

Maybe

=SUMPRODUCT((MONTH(A2:A6)=5)*(B2:B6="yes"),(C2:C6))

 

[VoG]

Yes, you can. There must be a limit to how many conditions you can have but I doubt verey much whether you would exceed it. I've seen sone ginormous SUMPRODUCT formulas posted on here.

 

[Sektor]

CSE (press Ctrl+Shift+Enter):

=SUM((A1:A8=5)*(B1:B8="Yes")*C1:C8)

 

[VoG]

Also is there a restriction to the amount of Conditions that excel can use in these type of formulas ?

The limit in Excel 2010 is 255 arrays.

 

[blossomthe2nd]

Thanks VOG, Lets hope it doesnt get that complicated - that'd totally ruin my Friday !!!!

Thanks again

 

[blossomthe2nd]

Hi VOG

Can I ask One more thing, I am using this formula across spreadsheets and I'm getting a NUM# error.

=SUMPRODUCT(--(MONTH('[1.MASTER LOG.xls]Drawdown'!$B:$B)=2),--('[1.MASTER LOG.xls]Drawdown'!$O:$O="NO"),'[1.MASTER LOG.xls]Drawdown'!$H:$H)

What I wish to happen is look at column B check date is Feb, Check column O where it is No and then count how many results in Column H,

Can you advise why I would happen ?

when I changed the formula ( AS row 1 In log is merged) to the below I get a VALUE# error

=SUMPRODUCT(--(MONTH('[1.MASTER LOG.xls]Drawdown'!$B2:$B1000)=2),--('[1.MASTER LOG.xls]Drawdown'!$O2:$O1000="NO"),'[1.MASTER LOG.xls]Drawdown'!$H2:$H1000)

Thanks

Anne

 

[VoG]

That looks like it should work but I don't think you need .xls

=SUMPRODUCT(--(MONTH('[1.MASTER LOG]Drawdown'!$B2:$B1000)=2),--('[1.MASTER LOG]Drawdown'!$O2:$O1000="NO"),'[1.MASTER LOG]Drawdown'!$H2:$H1000)

Are there error values anywhere in those ranges?