SUMPRODUCT or SUMIF

[filky]

I am trying to create a formulae to sum a column of numbers where the
correspond to two criteria.

Column to sum is Column 7
Fisrt criteria is that the text in column 3 must be equal to the valu
of cell H29
Second criteria is that the date in column 1 must be less than or equa
to the date in cell H1

I have created the following formulae which returns a #REF! error

=SUMPRODUCT((INDEX(Bookings!C7:C500,7,3)=H29),(INDEX(Bookings!A7:A500,7,1)<=H1),(INDEX(Bookings!E7:E500,7,5)))

Can anyone help

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(Bookings!C7:C500=H29),--(Bookings!A7:A500<=H1),Bookings!
G7:G500)

If you want to sum column G (column #7)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

When you have 2 more conditions that must drive the desires summing,
ordinarily you cannot use a SumIf formula.

Try...

=SUMPRODUCT(--(Bookings!$C$7:$C$500=H29),--(Bookings!$A$7:$A$500<=H1),--ISNU
MBER(Bookings!$A$7:$A$500),Bookings!$E$7:$E$500)

The range to sum appears to be in column E (from your attempted formula)
while you also say: "Column to sum is Column 7". So, adjust to suit.

 

[filky]

That is excellent. A new function for me, so thanks for the tip