Formula conversion for 2003

[SharpL]

Hello!

I'm currently (unfortunately) using Excel 2003, which does not have =SUMIFS

Please could you convert the below formula, so I can use in Excel 2003

Please Login or Register  to view this content.

I believe I require SUMPRODUCT, I'm not familiar enough with this formula to write it myself.

I can't upload a spreadsheet example on this computer - sorry!

Thanks in advance!

[Glenn Kennedy]

It's not your computer. it's this forum. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

[Special-K]

=sumifs(Data!F:F,Data!C:C,Table!$B$2,Data!B:B,Table!$D$6,T,Data!D:D,Table!A8)

=SUMPRODUCT((Data!C1:C1000=Table!$B$2)*(Data!B1:B1000=Table!$D$6)*(Data!D1:D1000=Table!A8)*(Data!F1:F1000))

Just regard all but the last set of brackets as an IF, ie IF (Data!C1:C1000=Table!$B$2), and the asterisk as an AND statement.
Note each range in brackets must be the same size, you cant have a SUMPRODUCT of (A1:A1000)*(B1:B50)

[SharpL]

This brings back #VALUE!

Hopefully, an example should now be attached

[SharpL]

I decided to do a work around, by creating a unique reference per line, via concatenate function.

Thanks for the help - If anybody has another solution for my future reference, that would be great

[xlnitwit]

Hi,

You need only adjust the ranges used

=SUMPRODUCT((Data!C18:C34=Table!$B$2)*(Data!B18:B34=Table!$D$6)*(Data!D18:D34=Table!A8)*(Data!F18:F34))