Combine INDEX and MATCH across Worksheets

[filriyadh]

Hi All,

I've been using excel for about 10 odd years now, only recently started to get in to some of the beefy stuff. This site has helped quite some.

Im basically making a roster file for our school clinic so that reports can be generated automatically and there are less hand-written records around.

I am having trouble with the 'Supplies' sheet, where I am attempting to sum up the quantities (column k) adjacent to all occurrences of a supply in Column J sheets 1 through 31. So that we can know how much of x supply was used in the whole month and so on.

Please help. Sorry if I was unclear in my writing.

=INDEX('24'!$J$3:$K$106,MATCH(B5,'24'!$J$3:$J$106,FALSE),2) Returns a value, but that is because sheet 24 has a value for b5, problem is if i use +INDEX and any of the sheets dont have a value for B5, the result is either VALUE or N/A
=SUMIF('1:31'!J5:J105,B6,'1:31'!K5:K105) gives a VALUE! error

I've also tried =INDEX('1:31'!$J$3:$K$106,MATCH(B5,'1:31'!$J$3:$J$106,FALSE),2) with no luck

 

[drsarao]

Welcome to board.
I think you need SUMIF.
However sumif cannot work across multiple sheets.
You need to SUMIF the quantity in each worksheet in a column eg
in A1=SUMIF(J:J,"Chalk",K:K)
Then in "Supplies" sheet you can consolidate with:
=SUM(1:31!A1) (I don't know if Excel will allow number only worksheet names. Try other names also.
Hope it helps.

 

[filriyadh]

thanks for the response n welcomes.

I have a working formula but its too long and doesnt save properly on excel 2003, works on excel 2007 though

i basically need a shorter version of the working formula

=SUM ( SUMIF ('1'!$J$2:$J$106, B8,'1'!$K$2:$K$106), SUMIF ('2'!$J$2:$J$106, B8,'2'!$K$2:$K$106), SUMIF('3'!$J$2:$J$106, B8,'3'!$K$2:$K$106), SUMIF('4'!$J$2:$J$106, B8,'4'!$K$2:$K$106) .... SUMIF('31'!$J$2:$J$106, B8,'31'!$K$2:$K$106)

 

[drsarao]

A heavy duty but direct approach without intermediate cells would be:
=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A31&"'!J:J"),"Chalk",INDIRECT("'"&A1:A31&"'!K:K")))
Where A1 to A31 carry Sheet names 1 to 31.

 

[drsarao]

.

 

[mvptomlinson]

Re: SUMIF across Worksheets

You might try the following array formula (press CTRL+SHIFT+ENTER, not just ENTER):

=SUM(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!J:J"),B8,INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!K:K")))

 

[T. Valko]

Where A1 to A31 carry Sheet names 1 to 31.

If the sheet names are the sequential numbers 1 to 31 you don't need to list those in a range of cells.

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!J:J"),"Chalk",INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!K:K")))

 

[axa]

If the sheet names are the sequential numbers 1 to 31 you don't need to list those in a range of cells.

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!J:J"),"Chalk",INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!K:K")))

this should answer filyadh question

 

[mvptomlinson]

As expected would happen... also answered here. Same time as #NAME?'s response too!

duplicate post removed by Admin

 

[T. Valko]

I had already replied and then saw it was a duplicate post.

But hey, great minds think alike!