Using SumIFS with Index and Match

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. 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)
 

Attachments

  • SumIFS.JPG
    SumIFS.JPG
    51.8 KB · Views: 9

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,941
If the interval to be summed is on rows $2:$200000 then the criteria interval should similarly be 199999 lines long (ie cannot be $C$1:$Z$500)
Also, INDEX needs as first parametre an array (multi column) whereas you use a single column list ( $C$2:$C$200000)

Bye
 

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
133
Office Version
  1. 365
Platform
  1. Windows
Thank you for your help.
It has been really very helpful.

I do have another query that I sent in the other day that no one has replied, I wondered if you would be able to help. (See below)


I'm trying to set a dynamic range in a chart
My header is in cell E16 and my data can be in cells E17 to E517 or more
For this example my data is only 200 cells created from formulas in the cells so I have 300 blank cells with formulas in.
I don't know how to make the dynamic range ignore the formula blank cells?
Any help would be appreciated.
Thanks

=OFFSET('Start Data1'!$E$16,0,0,COUNTA('Start Data1'!$E$17:$E$517)-1,1)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,637
Members
410,862
Latest member
uskudar
Top