SumIF in a single horizontal row (range and sumrange in same row)

[noclueaboutexcel]

Hi,

I want to find the sum of a row with the range being from cells E2 to I2 and sum range from J2 to N2, cell E2 corresponds to J2,F2 to K2 and so on.
My sheet is very big with nearly 100,000 rows, Ive attached a sample sheet for your reference.

Best Regards

[Jonmo1]

Try

=SUMIF($E2:$I2,"First",$J2:$N2)

[johnmpl]

Hi, noclueaboutexcel!

Write in
[B1] = First, [C1] = Second, [D1] = Third

Try this:
[B2] : =SUMIF($E2:$I2,B$1,$J2:$N2)
Formula B2 drag right and down.

Check file. Blessings!

[noclueaboutexcel]

Hi it worked with absolute reference when i tried it without absolute references it didnt work.can you explain why? anyways thanks youve been really helpful

[noclueaboutexcel]

Yes both the ways are working thanks a lot @Jonmo1 and @johnmpl

[johnmpl]

Hi, again!

it worked with absolute reference when i tried it without absolute references it didnt work.can you explain why?

This formula:

[B2] : =SUMIF($E2:$I2,B$1,$J2:$N2)

Works with mixed reference, not absolute reference.

Why I need mixed reference?
Because if I drag the formula to the right, I need that the columns don't move (dollar sign before the letters)
but when I drag the formula down, I need that the rows move (without dollar sign before numbers).
This was for ranges E2:I2 and J2:N2

Is the opposite for B1 reference.

Blessings!