SUMIF function + skipping 1 column

[Echo4211]

Hi there,

I got a SUMIF function, that needs to skip 2 columns in each following cell. I have highlighted the part of the range that needs to change in orange for better visual.

For example:

I input in cell D3 the following formula
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!$C$3:$C$5000)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!$D$3:$D$5000)

And when I drag to the right, to D4 I would like to see the following formula:
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!$E$3:$E$5000)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!$F$3:$F$5000) and so on. Eventually the formula will be dragged from:to D3:Z5000

I have a feeling it will be something related to OFFSET, but I'm not skilled enough in Excel to get this to work :/

Can someone help?

Thank you in advance!

[JohnTopley]

Deleted by JT

[daboho]

If to right only change $
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!C$3:C$5000)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!D$3:D$5000)

If down
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;Offset('Sheet1'!$C$3:$C$5000,,row(A1)-1)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;Offset('Sheet1'!$D$3:$D$5000,,row(A1)-1)

[ChemistB]

without OFFSET

Please Login or Register  to view this content.

[wk9128]

D3 cell formula , drag it down

[Echo4211]

If to right only change $
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!C$3:C$5000)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;'Sheet1'!D$3:D$5000)

If down
=SUMIF('Sheet1'!$B$3:$B$5000;C$3;Offset('Sheet1'!$C$3:$C$5000,,row(A1)-1)+SUMIF('Sheet1'!$B$3:$B$5000;C$3;Offset('Sheet1'!$D$3:$D$5000,,row(A1)-1)

Hey, thank you for your reply, when I remove the $ symbols in front of the letters, then it only changes 1 column, not two. I want C to jump to E, and I want D to F, and then E to G and F to H, if you get my drift?

Just to give additional information, the cell needs to be dragged to the right. Also, when I tried the pull down formula that you provided, I get an error saying "The formula is missing an opening or closing parenthesis.", and it's pointing out to the last -1) part of the formula, but anyhow, it needs to change 2 columns, when dragged to the right.

[Echo4211]

without OFFSET

Please Login or Register  to view this content.

Doesn't seem to do anything, just to be clear, I am dragging this formula to the right, cause I see "ROWS", and I assume it's meant to be Columns, but after changing it I get an #SPILL! error, but both ways the formula doesn't work, except for the initial cell itself :/

[JohnTopley]

Answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[Echo4211]

I have added a file to this post (I hope), with an example, basically I want in the tab "Overview" the range in the SUMIF change as followed when dragging to the right, eventually 'John Smith' will be x1000 of other names, so this formula will be continued on C4:C5000 eventually.:

C3: =SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$C$3:$C$15)-SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$D$3:$D$15)
D3: =SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$E$3:$E$15)-SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$F$3:$F$15)
E3: =SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$G$3:$G$15)-SUMIF(Detailed!$B$3:$B$15;Overview!$B3;Detailed!$H$3:$H$15)

C3: SUMIF calculates C - D
D3: SUMIF calculates E - F
E3: SUMIF calculates G - H

[JohnTopley]

Try

in C3

=SUMIF(Sheet1!$B$3:$B$50,$B3,INDEX(Sheet1!$C$3:$Z$50,MATCH($B3,Sheet1!$B$3:$B$50,0),COLUMNS($A2:A2)*2-1))-SUMIF(Sheet1!$B$3:$B$50,$B3,INDEX(Sheet1!$D$3:$Z$50,MATCH($B3,Sheet1!$B$3:$B$50,0),COLUMNS($A2:A2)*2-1))

[Echo4211]

Try

in C3

=SUMIF(Sheet1!$B$3:$B$50,$B3,INDEX(Sheet1!$C$3:$Z$50,MATCH($B3,Sheet1!$B$3:$B$50,0),COLUMNS($A2:A2)*2-1))-SUMIF(Sheet1!$B$3:$B$50,$B3,INDEX(Sheet1!$D$3:$Z$50,MATCH($B3,Sheet1!$B$3:$B$50,0),COLUMNS($A2:A2)*2-1))

Hey JohnTopley,

At first glance, I have played around a bit on my example sheet, and it seems to work exactly how I want it for the first line, but when adding other names, the calculations seem to be off. How can I properly drag this cell downwards?

Could it be related to the COLUMNS($A2:A2)*2-1) that jumps along? (Updated file in attachment)
C4: =SUMIF(Detailed!$B$3:$B$50;$B4;INDEX(Detailed!$C$3:$Z$50;MATCH($B4;Detailed!$B$3:$B$50;0);COLUMNS($A3:A3)*2-1))-SUMIF(Detailed!$B$3:$B$50;$B4;INDEX(Detailed!$D$3:$Z$50;MATCH($B4;Detailed!$B$3:$B$50;0);COLUMNS($A3:A3)*2-1))

[JohnTopley]

Not certain why SUMIFS failed si I reverted to the much more "robust" SUMPRODUCT

=SUMPRODUCT((Detailed!$B$3:$B$50=$B3)*(INDEX(Detailed!$C$3:$Z$50,0,COLUMNS($A$2:A$2)*2-1)))-SUMPRODUCT((Detailed!$B$3:$B$50=$B3)*(INDEX(Detailed!$D$3:$Z$50,0,COLUMNS($A$2:A$2)*2-1)))

[ChemistB]

Try this in C3 copied across and down

Please Login or Register  to view this content.

[JohnTopley]

Missed the obvious of using "Product" as the index BUT is this the real situation ???

[wk9128]

If the form is so regular, the formula can be simple.

worksheet name : Overview
Cell C3 formula , Drag down and accross

[Echo4211]

Try this in C3 copied across and down

Please Login or Register  to view this content.

Thanks for your reply, the formula does work on the example sheet, but unfortunately certain products have identical names in my actual list, but needs to be calculated separately. I should of mentioned this in my initial post, my apologies for that.

[JohnTopley]

Have you tried post #12?

[Echo4211]

Have you tried post #12?

I have already tried it, but I'm trying to figure out why it works PERFECTLY, but when I extend the range =SUMPRODUCT((Detailed!$B$3:$B$50=$B3) to =SUMPRODUCT((Detailed!$B$3:$B$50000=$B3) it gives N/As all over the screen

[Echo4211]

It seems to be working now, I just need to make sure that all the ranges (and not just one) go from 50 to 50000. It does slow down the Excel by a bit though, but it works

Thank you JohnTopley, and everyone else for the help! I appreciate it!

[JohnTopley]

SUMIF now corrected

=SUMIF(Detailed!$B$3:$B$50,$B3,INDEX(Detailed!$C$3:$Z$50,0,COLUMNS($A2:A2)*2-1))-SUMIF(Detailed!$B$3:$B$50,$B3,INDEX(Detailed!$D$3:$Z$50,0,COLUMNS($A2:A2)*2-1))

[Echo4211]

SUMIF now corrected

=SUMIF(Detailed!$B$3:$B$50,$B3,INDEX(Detailed!$C$3:$Z$50,0,COLUMNS($A2:A2)*2-1))-SUMIF(Detailed!$B$3:$B$50,$B3,INDEX(Detailed!$D$3:$Z$50,0,COLUMNS($A2:A2)*2-1))

Works as a charm, and feels way smoother Thanks again JohnTopley!