No, no, no, no, no I’m not talking about the latest 3D animated movie.

I’m talking about how you can use SUMPRODUCT with SUMIF and INDIRECT to conditionally summarise data on multiple worksheets, for example when you’re creating a summary sheet in your workbook.

## First the data:

I’ve got 12 sheets just like the one below, one for every month – see the tabs at the bottom.

And I’ve got my summary sheet that totals up the data for each builder by region:

## Here’s the problem (read ‘fun challenge’)

1. The data for each month contains multiple entries for some builders, so I can't simply sum it.

2. You can’t use the SUMIFS function across multiple sheets…well not on its own.

## Solution 1: The slow option

If you’ve got oodles of time and a super computer you could add one SUMIF to another in one massive long formula like this:

=SUMIF(Jan!$A$3:$A$8,Summary!A5,Jan!B3:B8)+SUMIF(Feb!$A$3:$A$8, Summary!A5,Feb!B3:B8)+SUMIF……

Or.

## Solution 2: The fast option

You can use a formula like this (from cell B5 of the summary sheet):

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A3:A8"),$A5,INDIRECT("'"&tabs&"'!B3:B8")))

Enter your email address below to download the sample workbook.

**Download the workbook used in this example**. Note: The workbook download is a .xlsx file. If your browser changes the file extension to a .zip or .xml then you will need to replace it with .xlsx before saving the file.

## How this formula works

I’m not going to go into massive detail as you can read up on SUMPRODUCT here, SUMIF here and INDIRECT here but I will to point out a couple of things.

If you know how to use SUMIF then you will recognise that cell A5 contains your criteria, in this case the Builder’s name.

The other thing to note is the reference ‘tabs’. This is the named range given to the list of my sheet tab names located in cells G2:G13:

Note: you don’t need to give your list of sheet tabs a named range. You could simply reference the cells like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$G$2:$G$13&"'!A3:A8"), $A5,INDIRECT("'"&$G$2:$G$13&"'!B3:B8")))

Back to why we need to create a list of sheet tabs…listing out the sheet tab names enables you to use the INDIRECT function to create the reference to each sheet on the fly, which results in a short elegant formula.

## The Bad News

Because sometimes we have workbooks that contain a crazy number of sheets with complicated names you might be daunted by the idea of creating a list of the sheet names.

## The Good News

Next week I’ll show you how to extract a list of your sheet tab names with some dead easy VBA. Seriously, it’s copy and paste kind of stuff.

Ishwar Singh says

Very useful formula. Thanks.

adrin says

Hai guys.. I had experiencing difficulty in using formula sumproduct + sumif + indirect in calculating only “Actual” Total Sale on each sheet. Imagine that anybody can give a formula for the actual Total Sales data summarized into the Summay sheet. Examples of data on each sheet are as below.

Sheet1

Division Type Sales Retail

Division 11-Total Budget 792,510.00

Division 11-Total Actual 764,150.28

Division 12-Total Budget 1,358,200.00

Division 12-Total Actual 1,514,622.54

Division 13-Total Budget 1,322,000.00

Division 13-Total Actual 1,084,751.09

Division 14-Total Budget 49,000.00

Division 14-Total Actual 21,093.32

Sheet2

Division Type Sales Retail

Division 11-Total Budget 296,000.00

Division 11-Total Actual 265,594.15

Division 12-Total Budget 416,000.00

Division 12-Total Actual 305,710.21

Division 13-Total Budget 440,100.00

Division 13-Total Actual 527,700.01

Division 14-Total Budget 29,000.00

Division 14-Total Actual 11,126.42

Sheet3

Division Type Sales Retail

Division 11-Total Budget 473,000.00

Division 11-Total Actual 335,234.91

Division 12-Total Budget 394,000.00

Division 12-Total Actual 374,468.35

Division 13-Total Budget 829,154.00

Division 13-Total Actual 649,062.53

Division 14-Total Budget 56,000.00

Division 14-Total Actual 11,330.83

Sheet4 (Summary)

Division Total Sale(Actual) Total Budget

Division 11-Total

Division 12-Total

Division 13-Total

Division 14-Total

Catalin Bombea says

Hi Adrin,

Can you please upload a sample file on our forum? It will be easier for us to provide a functional formula and test it on your data structure.

Regards,

Catalin

azri adrin says

Hai Catalin.. the sample file had been uploaded.. Topic name is “Need sum Sale (Actual) from multiple sheet”.

Hopefully getting feedback from you soon..

Thanks..

Catalin Bombea says

Looks like you already got a functional formula 🙂

Regards,

Catalin

Simon Preston says

Awesome! Thank you.

Daniel Deak says

Hi! Could you help me with this modification?

In the function above can you use a criteria range instead of a single cell criteria?

like change the criteria in the function above from $A5 to A$5:A$9 or something

I have the same problem, but i want to sum based on multiple criteria. It seems to work on a single sheet but when i try to apply it in this formula it’s not working.

this is how i try:

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&$G$2:$G$13&”‘!A3:A8”), $A5:$A9,INDIRECT(“‘”&$G$2:$G$13&”‘!B3:B8”))).

If i give a big criteria range it gives back “NA” error if i try a shorter range it gives back false numbers.

Thanks for the help!

Catalin Bombea says

Hi Daniel,

Can you upload a sample file so we can see what’s wrong? Use our forum to create a new topic and upload a file.

Catalin

michaele says

This is great! now, how can I apply this concept using the INDEX formula so that my formula be more dynamic?

Mynda Treacy says

Hi Michaele,

Replace the named range ‘tabs’ with a dynamic named range.

Mynda

Michaele says

Thanks for the quick response. Maybe I didn’t frame my question right. I like the named range in the example above. And after using the formula more, and finding the ‘address’ function in comments/replys below to make it so the formula can be dragged over and the column update, I don’t need to use the INDEX formula. However, the example given below

(=SUMIF(INDIRECT(“‘”&$D$27&”‘!$B$1:$B$368″),INDIRECT($D$27&”!$A$375″),INDIRECT(“‘”&$D$27&”‘!”&ADDRESS(1,COLUMN(C1),2)&”:”&ADDRESS(368,COLUMN(C1),2)))

Doesn’t play well when a line gets inserted on the underlying tabs (it doesn’t update the range reference). Any thoughts?

Michaele says

Actually perhaps describing my use might be more helpful:

I am building a 10yr forecast model whereby there are underlying sheets (financial departments), which need to aggregate/roll up. I am familiar with INDEX combined with MATCH to pull back one value, but have not used it to pull back or aggregate multiple values from multiple sheets. Essentially that is what I am looking to do and I stumbled upon the above as a way to sum multiple tabs based on criteria.

Mynda Treacy says

In that case I would stop right there before it’s too late to fix the layout of your model. You should never spread source data over multiple sheets. Your source data should always be in a tabular format. This way you don’t have to wrangle Excel formulas to do things they weren’t designed for. Writing unnecessarily complicated formulas like this will only undermine the robustness of your model.

When your source data is in a tabular format you can easily use PivotTables to extract subsets of your data, or SUMIFS formulas to recreate those individual sheets, if that’s the final view you want.

Mynda

Bhawna Joshi says

=SUMIF(INDIRECT(“‘”&$D$27&”‘!$B$1:$B$368″),INDIRECT($D$27&”!$A$375″),INDIRECT(“‘”&$D$27&”‘!C$1:C$368”))

In this formula if I want C$1:C$368 to change as per column(when dragging) , how to do that. Please help

Catalin Bombea says

Try this version:

Soren says

Great tutorial.

I have made a formula to extract the tab names (to avoid VBA)

=RIGHT(CELL(“filename”;A1);LEN(CELL(“filename”;A1))-FIND(“]”;CELL(“filename”;A1)))

Then it is easy to refer to the tab names with a formula and changing the name of a tab will not cause the SUM IF formula to fail.

Mynda Treacy says

Nice tip, Soren. Thanks for sharing.

Mynda

Fred says

I have few sheets within a workbook all having the same format.

Each sheet has Row 1 with B1..AF1 as date example from 1-May to 31-May

Column A2…A10 are various types of component names.

All sheets again uses the same date row and column name description.

We assume sheet 1 to sheet 3.

I want to create a summary page with Column description the same, but I just need to key into the cells selected as the following :

Sheet Name : just enter SHEET 3

Start Date: just enter 1-May

End Date: just enter 2-May

Once I key in the Sheet name with the date range selected, the Column will automatically show me all the total value of the quantity within the date range selected.

Can you help me on this?

Catalin Bombea says

Hi Fred,

Can you please upload a sample file to see your data structure? You can use our Help Desk (create a new ticket), it will be easier to provide a personalized answer, to match your structure.

Catalin

Fred says

Hi Catalin,

Thk u for the prompt reply. I have already send my sample through the new ticket.

Regards

Fred

David Briggs says

Great Formula!

Can I assume that the INDIRECT function is generating a multidimensional array that ultimately gets resolved by the SumProduct function? I tried using F9 to evaluate pieces of the formula but it doesn’t allow me to see the entire array.

Thanks

Catalin Bombea says

Yes, you got it right, INDIRECT will create a range from that text string.

If you select the entire argument of the INDIRECT function

and press F9, excel will show the ranges created:`"'"&tabs&"'!A3:A8"`

{“‘Jan’!A3:A8″;”‘Feb’!A3:A8″;”‘Mar’!A3:A8″;”‘Apr’!A3:A8″;”‘May’!A3:A8″;”‘Jun’!A3:A8″;”‘Jul’!A3:A8″;”‘Aug’!A3:A8″;”‘Sep’!A3:A8″;”‘Oct’!A3:A8″;”‘Nov’!A3:A8″;”‘Dec’!A3:A8”}

If you select the entire function,

and press F9, excel will give you the content of those ranges:`INDIRECT("'"&tabs&"'!A3:A8")`

{“Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”;”Dave”}

If the range is too large, excel fails to display the data, there is a limit in the length of the text displayed.

Cheers,

Catalin

John Galich says

Hi,

This site is a great find and a gold mine of information. This process is very, very close to helping me solve what I want to do, but not quite.

I have a 12 tabs in a spreadsheet (one for every month) plus a summary/year-to-date (YTD) sheet where we track the sales of each of our 23 reps for three core product categories. Each month and rep also has a forecast for each core product category already built into each month tab.

What I want to do is a YTD “SUM IF” of the forecast fields in the Summary tab based on the month being processed. For example, if this is May 1, I would want to sum the forecasts for January through April only. Any suggestions are welcome.

Thank you!

John Galich.

Catalin Bombea says

Hi John,

Can you please upload a sample file with your data structure on our Help Desk? (create a new ticket). It will be easier to provide a personalized answer.

Cheers,

Catalin

jas says

Hi guys,

I am just starting with Excel, I know how to use formulas like sumif, indirect or sumproduct. But all of them by its own. I am trying to solve the example. As I am trying to really understand what I am doing, how can i use this kind of symbols, without just copying the formula that you give me?

”

“‘”

&

Thank you very much

Catalin Bombea says

Hi,

The single quotes (or apostrophy) are used in sheet names references, if the sheet name contains spaces , like this simple reference to a cell in another sheet:

=’Test Sheet’!B4 . If the sheet name does not contain spaces, the apostrophies are not needed: =Sheet1!A1

The double quotes are used in text strings, like: =”John Doe”

The & simbol is used for joining together multiple pieces of text, like:

=”John “&”Doe”

You will get the same result by using the CONCATENATE function: =CONCATENATE(“John “,”Doe”)

Hope this clarifies some things 🙂

Cheers,

Catalin

jas says

Thank you!

jas says

Just one thing. Like the example we are using:

…INDIRECT(“‘”&$G$2:$G$13&”‘!A3:A8″…

What I understand is:

“‘”= reference text which means nothing, no?, and

range G2:G13 and

“‘!A3:A8” another text reference with only one single quote

Catalin Bombea says

Hi,

A reference to a range from another worksheet looks like this:

=’Sheet 1′!A3:A8

Note that only the worksheet name is found between single quotes, before the exclamation mark.

In INDIRECT function, we recreate this reference structure by joining together multiple pieces of text strings:

=INDIRECT(“‘” & G2:G13 & “‘!” & “A3:A8”)

G2:G13 must hold the worksheet names list, as you can see we have to add a single quote before and after the worksheet names, folowed by the exclamation mark and range reference.

Cheers,

Catalin

Isabella says

Hi,

I have a worksheet and I was able to use this function perfectly. I just have an issue for some data that have codes within them. So for example in tab Jan instead of having Doug, I have WAVE_1 (Doug), and then for February it would be something like WAVE_2 (Doug), is there a way to use index match to find content within the cell instead of match the cell value?

Thank you,

Isabella

Mynda Treacy says

Hi Isabella,

Yes, probably, but I’d be inclined to separate the name out of the cells rather than try and coerce a formula into handling it.

You can use Text to Columns to extract the name in a few clicks.

Kind regards,

Mynda

Winnie says

Hi there,

Just wondering if this formula works if the ‘Dougs’ on Jan-Dec worksheets are in different cells.

So far, all Dougs are in cell A3.

What if:

Jan: Cell A3

Feb: Cell A16

Mar: Cell A9

Thank you!

Mynda Treacy says

Hi Winnie,

It will work if the ‘Dougs’ are in different cells in column A on each sheet.

Item 1 under the heading “Here’s the problem (read ‘fun challenge’)” says”

“The data for each month contains multiple entries for some builders, so I can’t simply sum it.” So therefore if you have your matching name in a different cell in column A it will work.

You just need to alter the range to include all the possible rows e.g. instead of A3:A8 make it to at least A3:A16, since your example says ‘Feb’ data is in cell A16. Likewise for the column B range. The formula below allows for up to row 100:

Kind regards,

Mynda

André says

Hi Mynda,

Thank you for this useful formula

Can you please tell me if there is a possibility to use this formula if central, east, north & South on Jan-Dec worksheets are in different cells.

Thank you

André

Mynda Treacy says

Hi André,

No, it requires the region to be in the same column.

Mynda

Chee Teik Ong says

Hi Mynda,

This is a very interesting excel trick! But what is the purpose of having SUMPRODUCT in the formula? I know that the formula will not work without it, but can’t really figure out the reason behind it.

Can you please explain?

Thanks.

Mynda Treacy says

Hi Chee Teik,

SUMIF on its own can’t handle a 3D reference, but if you pass the resultant array to SUMPRODUCT it can sum up the values.

Kind regards,

Mynda.

Chee Teik Ong says

Thanks Mynda, this really opens my eyes- SUMPRODUCT 3D.

MF W says

I like your trick in creating the ‘tabs’ with INDIRECT. Nice! 🙂

Regarding your example, I would actually prefer Data Consolidation to setting formulas, given that the data in different worksheets are static.

What do you think?

Carlo Estopia says

Hi MF W,

Yes, you’re right. It’s different strokes for different folks.

However, I have my opinion that it’s easier to add one more item–builder in this case– and

in case of an additional sheet by just adding one more sheet name to the ‘tab’ range, and then just

copying the formulas without much ado to the new item(builder) to get the same results.

I think it’s harder — by a teenie-weenie only –for me to redo the formulas

to a new item(builder) using Data Consolidation in this case especially with an additional

sheet. But of course this is just my opinion and very negligible. It doesn’t really matter which you choose.

It’s just a matter on what you’re used to. It’s just like some people would prefer

keyboard shortcuts to a mouse.

Cheers,

CarloE

Mark says

Excellent expaination of a very complex issue – thank you, that was very helpful.

Mynda Treacy says

Cheers, Mark 🙂

diogo says

doesn’t work with NA() is cell… any alternative to sum cross sheets with NA() ?

Mynda Treacy says

Hi Diogo,

Get rid of the NA errors by wrapping the formulas in an IFERROR function.

Kind regards,

Mynda.

Andrew says

Hi,

How would you alter the formula so that instead of just typing in D2 and checking for one name in one cell across all worksheets (ie. Doug), it checked and matched two names in two adjacent cells (ie Doug Brown in D2:D3)

Carlo Estopia says

Hi Andrew,

First of all, I hope we are talking about the same file here: Excel_Blog_3d_countifs.

I don’t think it’s possible. SUMIF or even SUMIFS is designed only to evaluate a criteria range only once. For example,

If the ‘Builder’ column is evaluated as you suggested once for Doug and once for Brown, SUMIF could not perform an ‘Or’ mechanism rather if the same range

is to be evaluated twice it will not result into anything because it will require that both ‘Brown and Doug’ criteria be satisfied and not either.

I hope it’s clear because I am not good at explaining 🙂

Cheers,

CarloE

stonecottage says

I need to track multiple tabs for both a date range and a name range. Working in one workbook, I have a tracking tab to summarize and the tab names are years (say 2002- 2013). I wanted to use the INDIRECT formula but am running into a road block. If I have a dynamic name range indicating a list of tab names. Should I be putting a name range on each tab as well? I have had to use the actual tab name to track but only am getting half way there. I can track date ranges from each tab but not name and date ranges together. (the list of dates and names also exist on the tracking tab for an easier formula.) Heres what I have so far.

=COUNTIFS(‘2002′!$M$3:$M$12081,”>=”&E$17,’2002’!$M$3:$M$12081,”<="&E$18)

Can you help?

Carlo Estopia says

Hi stonecottage,

Please send this file here: HELP DESK.

Please explain your problem further.

Cheers,

CarloE

Tobias Ford says

Hi there Mynda,

I’m having a problem with a workbook that i am working on and here is my scenario.

I have a range of tables across multiple sheets that all reflect safety stats. Each sheet has the same layout and structure and applies to different site locations. Each sheet is accompanied by a corresponding ‘graphical’ sheet which illustrates the stats in a graph.

What i am idealy attempting to do is to create a summary page using the same structure and tables as the site pages, yet summing pertinent values from the various sites.

EG hours worked is B Column and the first value occurs in B3.

I’m wanting to SUM the vaules from all the relevant site pages into the summary page, whilst excluding the graph pages.

Additionally i’m wanting the formula to have drag down functionality.

I was thinking that sumproduct or the sumifs would be a good solution – yet a lot of the syntax here is going over my head.

Any help with a solution would be much appreciated

Kind regards

Tobias Ford

Carlo Estopia says

Hi Tobias,

Please send this through HELP DESK.

It would be easier if we can see this on file.

Otherwise we can only advise to go and read our posts

–and you guessed it right–:

SUMPRODUCT

SUMIFS

Cheers,

CarloE

MIHAELA TOMESCU says

PERFECT!!!! THANK YOU!!! YOU SAVED ME!

Carlo Estopia says

Hi Mihaela,

On behalf of Mynda,

You’re welcome!!!

Carlo

Berry Wilson says

Looks very good

Mynda Treacy says

Cheers, Berry 🙂

stephanie says

I have studied the functions used in this and I don’t understand why you use sumproduct AND sumif, why can’t you use sumproduct and indirect on their own?

Carlo Estopia says

Hi Stephanie,

Sumproduct can be use on its own with INDIRECT. However, for some weird reasons, It doesn’t work like the SUMPRODUCT,SUMIF,INDIRECT combined.

In short, Sumproduct can only use a 1-celled dynamic range. For example, In our article, We cannot use the ‘tabs’ range which has 12 months(data in it).

If you have downloaded the file already,

Try this variation in E5.

=SUMPRODUCT(INDIRECT(“‘”&tabs&”‘!E3:E8”)*(INDIRECT(“‘”&tabs&”‘!A3:A8”)=$A5))

you’ll get an ERROR. lol. but that’s not it.

Goto Formulas Ribbon, Name Manager, CLick ‘tabs’ and edit its range to just $G$2.

you’ll see that your error changes to 3 which is the value of Doug for the Jan sheet.

To be honest, I don’t know why it’s like this. I guess, SUMPRODUCT is way too complicated

a function that its creator just wanted to limit its range. Its complication is that you

can use +(OR) and *(AND) operators in a very diverse kind of way. Incidentally, SUMIFS will work

too despite its multiple criteria ranges/criteria feature. I guess -again- it’s more

predictable unlike SUMPRODUCT. Who knows someday they’ll improve this limitation.

Sincerely,

CarloE

Rashid Mahmood says

SUMIF FORMULA is =SUMPRODUCT(SUMIF(INDIRECT(“‘”&A2&”‘!O:O”), B2,INDIRECT(“‘”&A2&”‘!AE:AE”)))

Where

1 A2=SHEET NAME

2 O:O= CRITERIA RANGE

3 B2=CRITERIA

4 AE=SUM RANGE

Mynda Treacy says

Hi Rashid,

Do you have a question or are you just making a statement?

Cheers,

Mynda.

Rob says

Hello Mynda,

I’ve followed your advice, created a group named ‘tabs’ and listed all of the tab names in it.

I’m using several summary sheets in which I am adding wins and losses in the following groups: Overall, Conference, and Division. Your formula works perfectly for Overall and Conference wins and losses (which are in separate columns, i.e. Overall wins, Overall losses, Conference wins, Conference losses).

For Division wins and losses, my reference range is different, where instead of referencing a range of 12 cells, I’m using a range of six cells (also on the same spreadsheets). For some reason, the totals are not adding up for this group and I can’t figure out what’s different.

For the North Division wins, my formula is: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!V2:V7”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

While for the South Division wins, the formula is: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!V9:V14”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

V2:V7 & V9:V14 reference a spot on the ‘tabs’ worksheet where I have division standings. The ranges are correct, but the totals aren’t — that is if the team has a total >0. It is totalling ‘zero’ just fine.

Can you help?

Carlo Estopia says

Hi Rob,

I have tried some experiment on my own but It would be better if you send me the file through Help Desk so I can see the flow or logic of your formulas and the data it is based upon.

Sincerely,

CarloE

Rob says

Hi Carlo,

Thank you! I’ve submitted a ticket and attached my file.

Carlo Estopia says

Hi Rob,

I was quite lost there for awhile. 😉

Anyway, you said that your range V2:V9 is correct, but I beg to disagree because you’re using a SUMIF.

SUMIF works only if the rows of the sum range and the range criteria are of the same dimension.

in your case V2:V7 being the criteria could not locate P3:P14 except I guess between P3:P7 which would

qualify as of the same dimension–that is why I think It picked up several figures.

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!H3:H14”),$A4,INDIRECT(“‘”&tabs&”‘!P3:P14”)))

So that solves why your SUMIF isn’t working.

As to the expected, next problem…

I suggest — it’s just a suggestion– that you have a master list as to who’s in ACC North and who’s in ACC South. In that way we can easily put teams in the right division using an IF Function directly in your ACC Sheet… i.e. IF Virginia is North then our SUMPRODUCT function else 0. something like it.

I am saying this because I have tried to use the SUMPRODUCT using a combo of INDIRECT, ISNA,ISERROR,VLOOKUP and it just provided an inconsistent result… and I don’t know why.

Sincerely,

CarloE

Rob says

Hi Carlo,

Thank you for looking into this for me. I didn’t consider the restrictions in range and your explanation makes perfect sense.

If I can figure out how to look up a value in ranges x and y, then pull the information from range z, do this for a range of tabs adn sum the figures, then take that corresponding value and put it in either section a or b (depending on where finding the value(s) in x or y) of the summary sheet, I’ll be all set.

Carlo Estopia says

Hi Rob,

Do you still need help on that? I’ve been doing something like that last week and I got inconsistent results. I think I can do it on a sheet per sheet basis but not on a range of tabs using INDIRECT.

I will still try though. Do you like a customized VBA function?

I will send it to you through helpdesk.

Sincerely,

CarloE

Nick C says

Your formula =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H8,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

Works brilliantly for me but i need to amend slightly so that the formula counts the number of items in G18:G190 rather than sums them up. Replacing SUMIF with COUNTIF unfortunately doesnt work.

Also I need to amend the formula after that to count the number of items that are in I18:I190 (instead of G18:G190) that have the word “Bound” in it.

Any thoughts?

Carlo Estopia says

Hi Nick C,

I am sure I have answered a similar question before and I thought it was you but

I couldn’t find the thread.lol

Anyways, Nick try to position your cursor in your current formula and “CTRL+SHIFT+ENTER”

that will make an array formula of countif.

Read : Array Formulas

Cheers.

CarloE

Nick C says

I have figured out my count issue above – but i do need help on a sumif (or sumifs) query

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H10,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

the above formula has a condition that H10 needs to be in the E18:E190 range.

Why does my formula BELOW not work to have an additional condition that H11 needs to exist in range B18:B190?

=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&tabs&”‘!E18:E190”),$H10,INDIRECT(“‘”&tabs&”‘!B18:B190”),$H11,INDIRECT(“‘”&tabs&”‘!g18:g190”)))

Carlo Estopia says

Hi Nick,

Your syntax is wrong.

SUMIFS and SUMIF are two different things; Although, It shows similar results.

SUMIFS Syntax: SUMIFS(SUM Range, CriteriaRange1, Criteria1, CriteriaRange2, Criteria2…and so on)

In your case you’re trying to treat SUMIFS like it’s a SUMIF.

Cheers.

CarloE

PT says

Dear Mynda

Thank you very much for the brilliant work. I have two follow up questions. First, how do I write this formula if I want to locate this named range (“tabs”) on a different page from the formula? Ex. Formula is on worksheet named “summary” and the tabs named range is on worksheet named “tabs”. Second, if it is possible to name this formula, something like =Formula(J23), would it reduce the size of my file (now 300Mb)? Because I have to make this calculating the formula over 10000+ rows. I am having a very difficult time trying to figure out the syntax involved in the indirect function. What are the best tools to use to tutor myself? TIA

Mynda Treacy says

Hi PT,

You can put the tabs list on any sheet, just make sure when you give it the name that you set the ‘Scope:’ to Workbook.

You can read a tutorial on INDIRECT here.

In terms of giving the formula a name to reduce the workbook size, I don’t think it will work. Excel still has to perform the calculation 10000 times. SUMPRODUCT is known to be labor intensive for Excel when crunching through large workbooks.

I’m not sure of your exact formula but if your workbook is slow make sure your formula doesn’t reference any whole columns. Only reference the cells you need so that Excel isn’t doing any redundant calculations.

I hope that helps.

Kind regards,

Mynda.

PT says

Dear Mynda:

Hi its me again, PT. If have been very productive with versions of your formula above since my last question, because of your help. I am still finding Indirect intuitively challenging. My question. As a background, I originally had 10 worksheets and a single summary sheet in 1 file. Then I split off the 10 worksheets and put them into their individual workbooks (the single file would no longer open and my OS would freeze). So now I have 10 workbooks and a Summary workbook. Now I would like to take the formula from above:

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!J”&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!J”&ROW(J23))))

And create a Named Range for column “J” in each of the 10 workbooks. By doing this I figure I can insert new columns between “A” and “J” without having to rename every column that appears after “J” in my Summary workbook (i.e. columns K, L, … DA). Each column including column J is the same for each of the 10 workbooks. I have tried several formulas but I receive #REF! and #NAME! responses. For example I tried renaming every column J in the 10 workbooks, from row 2 to row 15,000, the name “Ticks” and then summing all the column J’s in the Summary file with the following formula:

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!”&Ticks&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!&Ticks&”&ROW(J23))))

If you could assist me I would very much appreciate it.

TIA

PT

PT says

Clarification: the bottom formula is

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!”&[Stock1.xls]Ticks&”!”&ROW(J23)),”>0″,INDIRECT(“‘”&tabs&”‘!&[Stock1.xls]Ticks&”!”&ROW(J23))))

TIA

Mynda Treacy says

Hi PT,

It’s easier if you can send me the file. Can you please log a ticket on the help desk and attach the file.

Cheers,

Mynda.

PT says

Hi Mynda,

I have tried your formula but it is not working for my challenge, so I am asking you for help.

I have 10 worksheets in an excel 2003 file, each representing a stock (i.e. 10 stocks), and each row represents one trading day, and each column represents different data or formulas derived from the data during each day. In column “J” of each day of each sheet, I get either positive or negative number. For example cell J23 on each sheet might contain -4, 2, 10, -3, -6, -5, -3, 6, 1, 1 (or whatever the case may be).

Then I have a summary sheet (worksheet 31) in which I would like to add up all the numbers that are negative, and all the numbers that are positive. In the example my two formulas should give the results -21 and 20. For the negative calculation I tried

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&Sheet1:Sheet10&”‘!J23:J23”),J23<0,INDIRECT("'"&Sheet1:Sheet10&"'!J23:J23")))

That's it. How do I do it? Can you help?

PT says

Just a clarification, worksheets 11 through 30 are all unrelated sheets. While my summary sheet is worksheet 31, it could be any sheet.

Mynda Treacy says

Hi PT,

You need to list the sheet tabs somewhere in your workbook and give them a named range, e.g. ‘tabs’. You then use that named range in your formula instead of ‘Sheet1:Sheet10’. Like this:

You can also download the Excel workbook used in the example above and have a look at what I did.

Kind regards,

Mynda.

PT says

Thank you Mynda. The formula works! However, I cannot copy the formula to the next row because the J23 remains J23 and does not change to J24, or J25, and so on. What should I do? TIA

Mynda Treacy says

Hi PT,

Here’s the formula for adding up cell J23:

You can then copy this down the column.

Kind regards,

Mynda.

Chris says

This post has been very helpful. Thank You. For the formula below, where you add the Row function to make the formula dynamic so that it can be copied. How do you enable it to be copied when it needs to cover a range (as opposed to just one cell as it is below)?

Thanks again.

Chris

Mynda Treacy says

Hi Chris,

Glad you found it useful 🙂

You can simply reference a range in the ROW function like this: ROW(A1:A10)

If you want the ROW function to always start at A1 then modify it to: ROW($A$1:A10)

Likewise if you want it to always reference A1:A10 then modify it to: ROW($A$1:$A$10)

Kind regards,

Mynda.

KIBRIA says

I AM TRYING SUMPRODUCT PROPER FORMULA BUT ANS IS REF# DON’T KNOW WHY IS THERE ANY HELP FOR ME

Mynda Treacy says

Hi Kibria,

You usually get a #REF! error when one of the formula parameters is pointing to an invalid range.

Kind regards,

Mynda.

Trent says

Or a blank cell in the list of cell names

Luis Franco says

Bravo!

Mynda Treacy says

🙂 Cheers, Luis.

Joe Lam says

The indirect part should be INDIRECT(“‘”&A1&”‘”&”!E:E”) for the apostrophe. It works