I have a table with Net and Gross data in 2 columns. In the rest of the columns there are years (2010,2011,2012,2013). For the years that have a 1 (and is not blank) is it possible to sum the corresponding values from Net or gross on the same row. Without using a pivot table.

Please look at the attached example.


Probably easy to do, but I am rubbish on excel!

Attached Files

Example.xlsx (10.6 KB, 20 views)

I am also a bit rubbish with Excel :-)

But, perhaps something like this would work:-

=IF(D2=1;A2+A5;0) returns -6
=IF(D5=1;B2+B5;0) returns 4
And
IF(D6=1;B3+B6;0) leaves the cell blank

So when they are strung together, the Gross Data B2:B8 column would look like this:-
=IF(D2=1;B2;0)+IF(D3=1;B3;0)+IF(D4=1;B4;0)+IF(D5=1 ;B5;0)+IF(D6=1;B6;0)+IF(D7=1;B7;0)+IF(D8=1;B8)

Perhaps someone with some skills can suggest a more "elegant" solution :-)

Hi zulugandalf,


You need to use SUMIF function to sum the corresponding values.

Please look at attached sheet.

Good Luck


----------
Raju Singh
New Delhi, India

Attached Files

sumif.xlsx (11.4 KB, 23 views)

Thanks works great!