New SUM challenge

Hi again,

I have a new challenge. Please check out the attached spreadsheet.

The blue coloured figures are fields I will be updating on a daily basis.

First Question:
For cell D4 I need a formula to get the total of (E4*F4)+(G4*H4)+(I4*J4)+ etc etc to the end of the row, obviously without having to do it manually if possible

Second Question:
For cell C4 I need a formula to get the total of E4+G4+I4+K4+ (ie. skipping a column), again to the end of the row.

I hope I explained myself well enough, thanks in advance any who attempt to help!

Martyn

Originally Posted by martynonline

For cell D4 I need a formula to get the total of (E4*F4)+(G4*H4)+(I4*J4)+ etc etc to the end of the row....

Try this

=SUMPRODUCT(E4:IU4,F4:IV4,MOD(COLUMN(E4:IU4),2))

hi all,

just wanted to come back to say thanks for the answers everyone gave!


in the end i used the first answer i received (happened to be from another board) and they seem to be working well so far. but thanks again for the extra solutions and taking the time to help . i put it below if anyones interested

Please Login or Register  to view this content.

I don't really see the advantage of the unnecessary complexity of the first when you can modify the second formula by adding a single range to get exactly the same result , i.e.

=SUMPRODUCT(--(MOD(COLUMN(E4:IS4)-COLUMN(E4)+1,2)=1),E4:IS4,F4:IT4)

Note: perhaps it's not an issue but that formula will treat any blanks within the range as a zero, the one you're using will treat them as 1s

yeah that was an issue i noticed, i had to go about filling the blanks, it wasn't the perfect solution thats for sure (maybe why it was my first reply )

i've given your revised code a shot and it seems to do a nicer job of it as i prefer to leave all the cells blank until i need to fill them so it makes it easier to spot errors so thank you again daddylonglegs! much obliged!