SUMIFS alternative for excel 2003 - sumproduct not working

Hello all

I have a SUMIFS formula which works in 2007 however I need backward compatibility with excel 2003 as I don't want to have to make 2 versions of the workbook. I've tried entering a sumproduct alternative after researching these forums however it returns the #VALUE error.

the formula that works in 2007 is:

=SUM(SUMIFS(KVS!C:C,KVS!A:A,Control!J40,KVS!B:B,{"0600","0615","0630","0645"}))

the sumproduct alternative which doesn't work :

=SUMPRODUCT(--(KVS!A1:A1345="Control!J40"),--(KVS!B1:B1345={"0600","0615","0630","0645"}),KVS!C1:C1345)

any help at all would be greatly appreciated

Adam

It is an array formula use Ctrl + Shift + Enter to confirm

Upload an example file with dummy data

Thanks

Regards

Thank You for your interest mahju. I have attatched a very small portion of the 3 sheets im working with. Also a cell with the sumproduct value error.

edit: the sumproduct example is attempting the same formula as B9
Kind Regards
Adam

This doesn't resolve your problem but remove the quotes around Control!J40

=SUMPRODUCT(--(KVS!A1:A1345=Control!J40),--(KVS!B1:B1345={"0600","0615","0630","0645"}),KVS!C1:C1345)

I suspect there is a problem in comparing a range to an array. My guess that you are trying to use

KVS!B1:B1345={"0600","0615","0630","0645"}

to determine for each element in that range whether it matches any value in the array. I don't think that will work but I do not have an alternative at this time that does.

Sorry I don't have something more concrete.

If you want to sum hourly values in column "C" use separate condition for each quarter You can use sumproduct as well as sum(if entered as array like this

=SUMPRODUCT((KVS!A1:A19=Control!J40)*(KVS!B1:B19="0600")+(KVS!B1:B19="0615")+(KVS!B1:B19="0630")+(KVS!B1:B19="0645"),KVS!C1:C19)

or

=SUM(IF((KVS!A1:A19=Control!J40)*(KVS!B1:B19="0600")+(KVS!B1:B19="0615")+(KVS!B1:B19="0630")+(KVS!B1:B19="0645"),KVS!C1:C19))

Both formula showed answer as 6 (2+3+1)

Regards

Thanks very much for your help mahju it's perfect!

edit: this may sound daft but the formula you have me works on the example i sent you, but it doesn't work on the actual sheet after i've changed the array values to A1:A1345 etc etc..

I get the result 66. however the result should actually be 15 after checking the data manually and looking at the existing sumifs result

Try this version

=SUMPRODUCT((KVS!A1:A1345=Control!J40)*(KVS!B1:B1345={"0600","0615","0630","0645"})*KVS!C1:C1345)

What sort of values do you have in column B? If those are the only possible value that begin with 06 then this might also work

=SUMPRODUCT((KVS!A1:A1345=Control!J40)*(LEFT(KVS!B1:B1345,2)="06"),KVS!C1:C1345)

Note, if you have headers in row 1 then the first formula I sugggested won't work, change the ranges so that they start at the first row with data

in column B the values are all 15 minute period timestamps 0000, 0015, 0030, 0045 etc etc. column a are all dates formatted as : 20110220. the idea is having a sheet consolidate all the 15 minute periods to hourly periods. I tried both of your formulae but they both returned 0.

I have attached the actual sheet im working from, just to see if there is anything in there you might notice that's stopping them from working.

The sheet which i've attempted the alternate formula on is 'Monday'.

edit: because the data is now from the previous week. in the control sheet. the dates in column I will all need an extra -7 applying to them.

Regards
Adam

...or this version in Monday B3 copied down. The time values are taken from column A so you don't need to change the formula

=SUMPRODUCT((KVS!A$1:A$1345=Control!J$40+0)*(LEFT(KVS!B$1:B$1345,2)=LEFT(A3,2)),KVS!C$1:C$1345)