Using SUMIF formula across multiple worksheets

[MelIvy]

Hi,

I have been struggling with this for quite some time now. I have a workbook that has many worksheets. The first two worksheets are overviews of the numbers found in the remaining worksheets. Each worksheet (excluding the first two) is a different country with many different categories on each page. I finally was able to come up with a formula that would get me what I am looking for but it will take a long time to adapt it to each category on each page. I am trying to condense the following formula:

=SUMIF(InterCountry!C:C,"=Local Coordinator Fee",InterCountry!E:E)+SUMIF(Taiwan!C:C,"=Local Coordinator Fee",Taiwan!E:E)+SUMIF(India!C:C,"=Local Coordinator Fee",India!E:E)+SUMIF(Thailand!C:C,"=Local Coordinator Fee",Thailand!E:E)

As you can see each range, criteria, and sum_range, although found on separate worksheets, are all listed in the same column letters (Column C,"=Local Coordinator Fee", Column E, respectively) for each individual formula. I have about 100 different criteria I need to adapt this formula to so if I can simply this, it would save a ton of time.

I tried incorporating INDIRECT into the formula as seen below with defining a name for the different sheets I want to use but I get a #VALUE error:

=SUMIF(INDIRECT("'"&SheetList&"'!C:C"),"=Local Coordinator Fee",INDIRECT("'"&SheetList&"'!E:E")).

Any help with this would be much appreciated!Thank you in advance.

 

[NARAYANK991]

Hi ,

You were almost there.

=SUM(SUMIF(INDIRECT("'" & SheetsList & "'" & "!C:C"),"=Local Coordinator Fee",INDIRECT("'" & SheetsList & "'" & "!E:E")))

will do the job.

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[MelIvy]

Hi Narayan,
Thank you for responding as quickly as you did!

Two things:
1. When I enter that formula into my workbook, I get #Name? as an answer...any ideas as to why?

2. Can you explain why I need =SUM in addition to =SUMIF?
Thank you!

 

[MelIvy]

I actually just realized why it was responding with #Name?...(there was an extra "s" in SheetList). However, now it is coming back as #REF! Any ideas?

 

[NARAYANK991]

Hi ,

SheetList would be a named range which would have all the tab names in it ; thus , suppose you allotted the range Z1:Z4 for this named range , then SheetList would have the following formula in its Refers To box :

=Sheetname!$Z$1:$Z$4

where Sheetname would be the name of the tab where your SUMIF formula is entered.

I am assuming that you would have the following tab names in the above range :

InterCountry
Taiwan
India
Thailand

Regarding your question on why the SUM is required , I think the reason would be as follows :

The SUMIF function accepts range references in its first and third parameters , while the second parameter is a criteria reference. Here what you are having for your first and third parameters are not range references , but an array of range references ; there is a range reference in each tab , and there is an array of tabs. Hence , when the SUMIF function returns its result , the result is not a single scalar value , but an array of values , with as many elements in the array as there are tabs.

An an example , you might have an array such as :

{357 , 413 , 111 , 22}

If you do not use the SUM around your formula , the cell would display just the first value 357.

Using the SUM will return the correct result of 903.

Narayan

 

[jeffreyweir]

MelIvy: My suggestion is to bring all the data into one sheet and use a PivotTable to summarize. Is that a possibility?