SUMIFS with OR

[gldurand]

Hi Gang

I have the following formula that works well, but i know need to compare 5 other values in the same column

=SUMIFS('Source Data'!AG1:AG65000,'Source Data'!BF1:BF65000,"VALUE A",'Source Data'!H1:H65000,"VALUE B")

I need to look at 5 other values in column H, how do I include an OR statement, i tried but this does not work

=SUMIFS('Source Data'!AG1:AG65000,'Source Data'!BF1:BF65000,"VALUE A",'Source Data'!H1:H65000,"VALUE B",'Source Data'!H1:H65000,"VALUE C,'Source Data'!H1:H65000,"VALUE D",'Source Data'!H1:H65000,"VALUE E",'Source Data'!H1:H65000,"VALUE F")

Is this worng, or is there another formula that might be better

Thanks for any assistance is greatly appreciated


Thanks

 

[jasonb75]

Untested

=SUM(SUMIFS('Source Data'!AG1:AG65000,'Source Data'!BF1:BF65000,"VALUE A",'Source Data'!H1:H65000,{"VALUE B","Value C","Value D","Value E","Value F"}))

 

[texasalynn]

here is an example of the choices for column H

=SUMPRODUCT(--('Source Data'!H1:H50={"VALUE A","VALUE B",,"VALUE C","VALUE D","VALUE E"}))

 

[Domenic]

Or...

=SUMPRODUCT(--('Source Data'!BF1:BF65000="VALUE A"),--ISNUMBER(MATCH('Source Data'!H1:H65000,{"VALUE B","VALUE C","VALUE D","VALUE E","VALUE F"},0)),'Source Data'!AG1:AG65000)

 

[gldurand]

Hi Domenic

It works great thanks, but i have one more ask of you. If i have the following and wanted to validate a date field to make sure my record was in between two dates, what change would I make

=SUMPRODUCT(--('Source Data'!$BF$1:$BF$65000=B8),--('Source Data'!$S$1:$S$65000="In The Call"),--ISNUMBER(MATCH('Source Data'!$H$1:$H$65000,{"1-Prospect (Discovering)","2-Qualify (Discovering)","3-Propose (Advocating)","4-Decision (Advocating)"},0)),'Source Data'!$AG$1:$AG$65000)

I want to add FIELD A (DATE) BETWEEN Jan 1 2011 and March 31st 2011

Thanks

 

[Domenic]

If, for example, Column I contains the date, C8 contains the start date and D8 contains the end date, try...

=SUMPRODUCT(--('Source Data'!$BF$1:$BF$65000=B8),--('Source Data'!$S$1:$S$65000="In The Call"),--ISNUMBER(MATCH('Source Data'!$H$1:$H$65000,{"1-Prospect (Discovering)","2-Qualify (Discovering)","3-Propose (Advocating)","4-Decision (Advocating)"},0)),--('Source Data'!$I$1:$I$65000>=C8),--('Source Data'!$I$1:$I$65000<=D8),'Source Data'!$AG$1:$AG$65000)

 

[gldurand]

Thanks Dom, it works, can i ask you another question.. I just posted another thread, can you have a look

Thanks my friend

 

[Domenic]

Thanks Dom, it works,...

Your welcome! Thanks for the feedback!

...can i ask you another question.. I just posted another thread, can you have a look

I've taken a look at your other thread and I see that you're in excellent hands.