Sumproduct not equal to

[Mtyler]

Hi

I've trawled through a few other threads on this and come up with the following formula:

=SUMPRODUCT(INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$bp$8:$bp$340"),1-ISNUMBER(MATCH(INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340"),Summary!$K$44:$K$53,0)),--(May!$E$8:$E$340=Summary!$M41))

Whereby INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$bp$8:$bp$340") is the range to be summed,

INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340") is the range to review for all values excluding those in found in the range Summary!$K$44:$K$53 (which are formula entries to give a whole integer)

and this needs to be carried out for entries where values in May!$E$8:$E$340 equal Summary!$M41.

Unfortunately, the whole formula doesn't work. May!$E$8:$E$340=Summary!$M41 works, but it doesn't exclude those entries where the range in INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340") does not equal Summary!$K$44:$K$53,0).

Any assistance to correct this formula would be greatly received, or any easier way to complete my task. Basically what I'm doing is producing a top ten results per state (from the multistate list) based on a rank formula, and then trying to use this formula to sum the remaining files in each state, excluding those included in the top ten analysis.

Thanks

Matt

 

[T. Valko]

Hi

I've trawled through a few other threads on this and come up with the following formula:

=SUMPRODUCT(INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$bp$8:$bp$340"),1-ISNUMBER(MATCH(INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340"),Summary!$K$44:$K$53,0)),--(May!$E$8:$E$340=Summary!$M41))

Whereby INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$bp$8:$bp$340") is the range to be summed,

INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340") is the range to review for all values excluding those in found in the range Summary!$K$44:$K$53 (which are formula entries to give a whole integer)

and this needs to be carried out for entries where values in May!$E$8:$E$340 equal Summary!$M41.

Unfortunately, the whole formula doesn't work. May!$E$8:$E$340=Summary!$M41 works, but it doesn't exclude those entries where the range in INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340") does not equal Summary!$K$44:$K$53,0).

Any assistance to correct this formula would be greatly received, or any easier way to complete my task. Basically what I'm doing is producing a top ten results per state (from the multistate list) based on a rank formula, and then trying to use this formula to sum the remaining files in each state, excluding those included in the top ten analysis.

Thanks

Matt

The formula works for me.

I'd write it a bit differently:

=SUMPRODUCT(--(ISNA(MATCH(INDIRECT(TEXT(EOMONTH(date,-1),"mmm")&"!CA8:CA340"),Summary!$K$44:$K$53,0))),--(May!$E$8:$E$340=Summary!$M41),INDIRECT(TEXT(EOMONTH(date,-1),"mmm")&"!BP8:BP340"))

 

[Mtyler]

T Valko

Thanks for your help - I've just figured out that I was using the wrong range

INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340")

needed to be

INDIRECT(TEXT((Date,"mmm")&"!$ca$8:$ca$340")

 

[T. Valko]

T Valko

Thanks for your help - I've just figured out that I was using the wrong range

INDIRECT(TEXT(EOMONTH(Date,-1),"mmm")&"!$ca$8:$ca$340")

needed to be

INDIRECT(TEXT((Date,"mmm")&"!$ca$8:$ca$340")

Good deal. Thanks for the feedback!