Summing Data based on certain criteria

[Howardc]

I would like to add the data highlighted in Col A, which I have named "other_Income". The values applicable to these items are in Columns F, I & J

I have used the sum function to show you what the answer should be. Could one use a range name and then an offset function to add the values based on the criteria or use sumifs?

It would be appreciated if you could assist me with a formula to add a series of data based on crieria such as this.

[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]

Hi

You could try this Array formula.

=SUM(IF(ISNUMBER(MATCH(A3:A13&"",A20:A25&"",0)),IF(ISNUMBER(MATCH(F1:J1,B20:B22,0)),F3:J13)))

where

A20:A25 - Other income criteria,
B20:B22 - the months

[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

[Nishant Choudhary]

great trick