SUMIFS INDEX and MATCH

[atearth]

Hi all,

I'm trying to use SUMIFS INDEX and MATCH but the answer I get is wrong.
Anyone know what the answer is to a problem I have?
Please see the image below.
Thank you in advance

=SUMIFS(INDEX($F$15:$M$18,0,MATCH(F$24,$F$14:$M$14,0)),$D$15:$D$18,$D25)

 

[awoohaw]

For Sumifs, the first argument needs to be a range, not a value returned by INDEX. You can build a reference from index however.

In your example can you explain what values are supposed to be summed?

 

[atearth]

From looking at commissioning in the month of Jan-23.

I'm not understanding your first comment.

 

[awoohaw]

the requirement for the SUMIFS function is that the first argument is a range reference..
the caclulation INDEX($F$15:$M$18,0,MATCH(F$24,$F$14:$M$14,0)) returns a value of "WBS Description" or "Outline Design".. I'm not sure the zero for "ROW" number is valid, but I could be wrong.

 

[awoohaw]

what value did you expect to get with that formula? And what is the location of the value (the cell reference)?

 

[awoohaw]

what value did you expect to get with that formula? And what is the location of the value (the cell reference)?

I need to clarify.. if you are summing a number of values.. please give me the addresses of all the cells you want to sum.

 

[atearth]

Cells I need to edit the formulas in
F25 G25
F26 G26
F27 G27
F28 G28

Values are in the following cells
F15 to M15
F16 to M16
F17 to M17
F18 to M18

If the formulas were working then it would look like the below.

 

[awoohaw]

From looking at commissioning in the month of Jan-23.

I'm not understanding your first comment

In both of the grid areas above the calculation you have numerous columns wiht Jan 23 date. How do you want to accomodate that?

 

[atearth]

That is correct as each one represents 1 week.
Am I to merge all 4 so they are 1?

 

[awoohaw]

I think showing Feb is fine I'm still figuring that out, but it can probably work with sumproduct.
(It will take me a while to figure that out, i'm not super good with that!). If you want to merge them you can but it would take some detail out of your information that you may not want to do.