How to group lookup values for SUMIFS when using Data validation list

Hey guys,

In the attached sample file, I have two sheets. "Data" sheet is the source and "Summary" is the reporting sheet. I have four product A, B, C and D. A and B are Legacy products but have different price values as shown in the "Data" Sheet. My goal is to select the option "Legacy" from the list in "Summary" sheet so that the table next to the list displays the combined values for A and B. I have a column highlighted in the sheet "Summary" to show the expected output. Could anyone help me with this?

Any help is appreciated.

Thanks in advance

VJ

nvm guys.

I added a helper column in the source "Data" Sheet to denote which Products are Legacy and tweaked the SUMIFS formula with this new column and it worked. I've attached the file with the updated formula and column.

thanks to anyone who had already started working on this.

VJ

You could use this formula in C4:

=IF($A$1="Legacy",SUM(SUMIFS(Data!$C:$C,Data!$A:$A,$B4,Data!$B:$B,{"A","B"})),SUMIFS(Data!$C:$C,Data!$A:$A,$B4,Data!$B:$B,$A$1))

then copy down as required.

Hope this helps.

Pete

My formula does not require that helper column of yours.

Pete

Pete and John

Appreciate the help you guys. I will try both your formula and see how it works out in my original report.

VJ

Glad it worked for you - thanks for the rep.

Pete