sumif and index match not working

[Dares2]

hi all,

I am trying to sum a group of values that looks for the text in cell B12 in a different sheet (shown below) and there would be several in the column then adds all these values based on an index of the month eg Aug in cell N8 (which would then add all the values for Jul and Aug (in a column of months from Jul (b24- Jun M24). The formula I am using is probably completely wrong but hope you can help. Thanks Dares2

eg SUMIF('Other sheet'A:A,$B12,INDEX(B:M,,MATCH($N$8,$B24:$M$24,0)))/1000

 

[Chrisdontm]

I am not following you all of the way, but, one thing I can say;

Is that the MATCH part of your formula has more than one column for a range to look up in.

You can only use one column in a MATCH formula, or it won't work.

 

[Aladin Akyurek]

Possibly...

=SUMIF('Other sheet'!$A$25:$A$1000,$B12,INDEX('Other sheet'!$B$25:$M$1000,0,MATCH($N$8,'Other sheet'!$B24:$M$24,0)))/1000

 

[Dares2]

thanks John for the feedback, yes I think match is possibly the wrong formula to use.

 

[Aladin Akyurek]

thanks John for the feedback, yes I think match is possibly the wrong formula to use.

And post #3 ?

 

[Dares2]

hi Aladin sorry for late reply. Yes I think it is a good formula but not sure this one was the one for me in this particular situation or I couldnt readily get it to work but wrote a different one in the end that was a sumproduct. Thanks again for taking the time too.

 

[Aladin Akyurek]

Thanks for the update. Just one note: It's more efficient to invoke SumIf(s) formulas when they apply instead of equivalent array-processing formulas.