How to Sum a range if 2 criteria are met? (different range sizes)

Jpress

Hi, I am trying to sum the total Inv. value in the below table but only for rows where the WBS target is contained in the WBS element AND where the Inv. Target Year appears in the range of Inv. years. In the below example, the the total would equal $500 because rows 1 and 2 contain 123 in the WBS Element column and the Inv. target year is in inv. year range. Any help is greatly appreciated!

Darren Mullen

@Jpress use SumIFs to sum the Inv. Amt Y1 based on Inv. Year 1 and another SumIFs to sum Inv. Amt Y2 based on Inv. Year 2 and add the two sumifs togther like this:

SUMIFS(Inv Y1 data) + SUMIFS(Inv Y2 Data)

Darren Mullen

@Jpress use SumIFs to sum the Inv. Amt Y1 based on Inv. Year 1 and another SumIFs to sum Inv. Amt Y2 based on Inv. Year 2 and add the two sumifs togther like this:

SUMIFS(Inv Y1 data) + SUMIFS(Inv Y2 Data)

Jpress

@Darren Mullen unfortunately that does not work because I need both the inv. target year AND the wbs target conditions to be met. Unless I am mistaken, using your formula in my example would sum the third row which is not what I am looking for. Thanks for taking a look at it though!

Jpress

@Darren Mullen thinking about this a bit more, I may have been too hasty in my response. I think I understand what you mean now. I'll give it a try and see if it works. Thanks again!

Jpress

@Darren Mullen works like a charm. Thank You!

Darren Mullen

@Jpress Great! Thanks for the follow up. and I forgot to mention the WBS aspect too! But you figured it out :)