Peter Davison
Active Member
- Joined
- Jun 4, 2020
- Messages
- 444
- Office Version
- 365
- Platform
- Windows
I have an "output" file where I want to sum sales based on a cell criteria. My sum values are in the Performance Sheet C2:C200000. My criteria could be 1 of many which is in cell B4 selected from a lookup list. The data is in another file in the Product Sheet in a range of Cells C1:Z500 (Headings that relate to cell B4 are in Row1 but could be in any column). I have a list of attributes associated with B4 in cells B5 to B15. My formula is in C5 to start with and will then be copied down to the other cells C6 to C15.
The formula below is returning "VALUE" I think I have something wrong in the last part of the formula C1:Z500, but I'm not sure what it needs to be?
I would appreciate any help you can give me please.
=SUMIFS(INDEX('[Breakfast Foods Combined Raw Data.xlsx]Performance Data'!$C$2:$C$200000,,MATCH($B$4,'[Breakfast Foods Combined Raw Data.xlsx]Product Data'!$C$1:$Z$1,0)),'[Breakfast Foods Combined Raw Data.xlsx]Product Data'!$C$1:$Z$500,"="&$B5)
The formula below is returning "VALUE" I think I have something wrong in the last part of the formula C1:Z500, but I'm not sure what it needs to be?
I would appreciate any help you can give me please.
=SUMIFS(INDEX('[Breakfast Foods Combined Raw Data.xlsx]Performance Data'!$C$2:$C$200000,,MATCH($B$4,'[Breakfast Foods Combined Raw Data.xlsx]Product Data'!$C$1:$Z$1,0)),'[Breakfast Foods Combined Raw Data.xlsx]Product Data'!$C$1:$Z$500,"="&$B5)