Summing Data based on certain criteria

[Rick Rothstein]

Create a new Defined Name that consist of the entire rows of 3:5,7,11:12 and name it OI (short for Other Income), then try this formula (those single spaces between OI and the column designations are on purpose)...

=SUM(OI F:F,OI I:I,OI J:J)

[Howardc]

Create a new Defined Name that consist of the entire rows of 3:5,7,11:12 and name it OI (short for Other Income), then try this formula (those single spaces between OI and the column designations are on purpose)...

=SUM(OI F:F,OI I:I,OI J:J)

Hi Rick

Thanks for the help, much appreciated. First time I've come accross this formula-very clever

Regards

Howard

[Rick Rothstein]

Hi Rick

Thanks for the help, much appreciated. First time I've come accross this formula-very clever

Two ranges separated by a single space returns the intersection of the two ranges. And, now that I think of it some more, we can take advantage of the fact that Columns I and J are contiguous and shorten the formula to this...

=SUM(OI F:F,OI I:J)

And, of course, instead of using my OI defined name, you can simply change your Other_Income defined name to be the entire rows and use that in place of my OI if you are not using Other_Income anywhere else in your workbook.

[Admin]

Two ranges separated by a single space returns the intersection of the two ranges. And, now that I think of it some more, we can take advantage of the fact that Columns I and J are contiguous and shorten the formula to this...

=SUM(OI F:F,OI I:J)

And, of course, instead of using my OI defined name, you can simply change your Other_Income defined name to be the entire rows and use that in place of my OI if you are not using Other_Income anywhere else in your workbook.

Wow ! nice one Rick, I have never seen this before.

[Howardc]

Hi Guys

Thanks for the help and explanations, much appreciated