SUMPRODUCT or SUMIF

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

Column to sum is Column 7
Fisrt criteria is that the text in column 3 must be equal to the value
of cell H29
Second criteria is that the date in column 1 must be less than or equal
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),(IND EX(Bookings!A7:A500,7,1)=H1),(INDEX(Bookings!E7:E 500,7,5)))

Can anyone help?


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany

"filky " schrieb im Newsbeitrag
...
I am trying to create a formulae to sum a column of numbers where
they
correspond to two criteria.

Column to sum is Column 7
Fisrt criteria is that the text in column 3 must be equal to the
value
of cell H29
Second criteria is that the date in column 1 must be less than or
equal
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),(IND EX(Bookings!A7:A500,7
,1)=H1),(INDEX(Bookings!E7:E500,7,5)))

Can anyone help?


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
I am trying to create a formulae to sum a column of numbers where they
correspond to two criteria.

Column to sum is Column 7
Fisrt criteria is that the text in column 3 must be equal to the value
of cell H29
Second criteria is that the date in column 1 must be less than or equal
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),(IND EX(Bookings!A7:A500,7,1)=
H1),(INDEX(Bookings!E7:E500,7,5)))

Can anyone help?


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/