Sumproduct - Non Adjacent Cells

[cshetty]

Dear Experts

Please help me with this formula to sum non adjacent cells based on criteria.

=SUMPRODUCT(((Schedule!$M$5:$M$2316),(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

Getting #Value error.

Thanks in advance.

 

[Special-K99]

Does this work, I just changed the comma to an asterisk?
=SUMPRODUCT(((Schedule!$M$5:$M$2316)*(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

 

[Peter_SSs]

.. or try just removing these two sets of parentheses, especially the blue ones.

=SUMPRODUCT(((Schedule!$M$5:$M$2316),(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

=SUMPRODUCT(Schedule!$M$5:$M$2316,(Schedule!$O$5:$O$2316)*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

 

[cshetty]

Dear Experts

Please help me with this formula to sum non adjacent cells based on criteria.

=SUMPRODUCT(((Schedule!$M$5:$M$2316),(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

Getting #Value error.

Thanks in advance.

Hi Special-K99
This isn't working. Results gives an exorbitant number.

 

[cshetty]

.. or try just removing these two sets of parentheses, especially the blue ones.

=SUMPRODUCT(((Schedule!$M$5:$M$2316),(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

=SUMPRODUCT(Schedule!$M$5:$M$2316,(Schedule!$O$5:$O$2316)*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

Hi Peter

This gives an exorbitant number. Must be multiplying with the date value as well.

 

[Peter_SSs]

In that case you need to carefully explain exactly what the formula is supposed to do or better still do that and give a small set of dummy data for both sheets with irrelevant columns hidden and include the expected result. If you choose carefully probably 10 or so rows from Schedule should be enough to demonstrate. XL2BB would be preferred so we can easily copy to test with.

Since yo gave us a non-working formula we had to guess what you wanted the formula to do. Not too surprising then if we guessed incorrectly.

 

[Fluff]

Another guess is

=SUMPRODUCT(((Schedule!$M$5:$M$2316)+(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

 

[cshetty]

In that case you need to carefully explain exactly what the formula is supposed to do or better still do that and give a small set of dummy data for both sheets with irrelevant columns hidden and include the expected result. If you choose carefully probably 10 or so rows from Schedule should be enough to demonstrate. XL2BB would be preferred so we can easily copy to test with.

Since yo gave us a non-working formula we had to guess what you wanted the formula to do. Not too surprising then if we guessed incorrectly.

Hi Peter
Mnn. I understand. Let me explain the scenario.

Range M & O has amounts payable. (Schedule Sheet)
Range D has due dates. (Schedule Sheet)
Range G has Client Nane. (Schedule Sheet)
Range B in my report sheet has Month beginning date.

In result cell I want the total of amounts payable from schedule sheet, for a particular client, if the due date in schedule sheet is matching the month & year in range B of report sheet.

to my original formula to work, easiest way would be to copy range O of schedule sheet and insert to range N . But the structure of the schedule sheet has to be this way.

Hope I explained it properly.

 

[Peter_SSs]

Hope I explained it properly.

Much clearer, thanks.
Doesn't that mean then that @Fluff guessed correctly and that formula works?

 

[cshetty]

Another guess is

=SUMPRODUCT(((Schedule!$M$5:$M$2316)+(Schedule!$O$5:$O$2316))*(Schedule!$D$5:$D$2316>=$B4)*(Schedule!$D$5:$D$2316<=EOMONTH($B4,0))*(Schedule!$G$5:$G$2316=P$3))

Dear Fluff

This is it. Thanks mate.