Sum corresponding cells on various sheets if a condition is met

Hi,

I'm quite new here and a self taught beginner so forgive me if my phrasing is poor and question rather simple.

I'll explain as best I can:

I have a work book with 10 sheet all with the same input set up to record figures. As it stands I have a summary sheet at the end that totals values from the 10 sheets using the following code:

=IF(SUM(Sheet1:Sheet10!C3)>0, SUM(Sheet1:Sheet10!C3),"")
=IF(SUM(Sheet1:Sheet10!C4)>0, SUM(Sheet1:Sheet10!C4),"")
=IF(SUM(Sheet1:Sheet10!C5)>0, SUM(Sheet1:Sheet10!C5),"")

and so on up to C30

I want to divide this information into 2 different sub totals, using the data validation option I have a list selection cell on each sheet with 2 options, lets call them "option a" and "option b" This list cell is located in J43 on all sheets.

As each sheet will be designated 1 or the another, I was hoping to be able to total up the values of just the option a's and then just the option b's. The part where it checks that the value is greater than zero isn't compulsory

I'll express my desire in terms of my dodgy attempt at what I think the code could potentially look like:

=SUMIF(Sheet1:Sheet10, J43="option a", Sheet1:Sheet10!C3)

So to reiterate, I want to add the values in cell C3 on the sheets sheets where cell J43 equals option a.

From there I can extrapolate the code myself to work in the neighboring cells and reverse the option to obtain all of the option b's.

Thank you for your time,

Mick.

Try:

Please Login or Register  to view this content.

copied down

WOW, that works exactly how I needed it to. I copied it down and tested it and its perfect. I was also able to manipulate it to show all the option b's and cell data from the same rows but different columns. I don't completely understand how it all works but I am able to use it to extract all the data I need.

To hide all of the cells returning zero I just formatted the cell as custom and used the following option:
0;-0;;@ The only down side was that I can no longer have therm formatted in the currency format like this (they display similar to a general number), but that's okay.

My coding attempt was way off, but at least you could understand my intentions. Thank you very much for that, I'll have to do some research into the SUMPRODUCT and INDIRECT commands so that I can better understand just how that code works.

Thanks again.

Note: Just noticed that if I rename the sheets the code doesn't automatically update.. Is there a simple way to allow the sheet references to update if the sheets names are changed? My original code just to total up references from all sheets would change the sheet names in the code automatically if the sheets were renamed.

This part: "'Sheet"&ROW(INDIRECT("1:10") says you have sheets named Sheet1 to Sheet10

Renaming sheets will throw off the formula.

If you rename them with a similar consistent naming scheme, for example Table1, Table2, Table3, etc... then you can adapt the formula easily enought... but if you rename the sheets uniquely like: ThisSheet, ThatSheet, Summary then you will need to create a list of those sheetnames somewhere and then select that range and name it through Insert|Name|Define (call it TabNames) and revise the formula to something like:

=SUMPRODUCT(SUMIF(INDIRECT("'"&TabNames&"'!J43"),"a option",INDIRECT("'"&TabNames&"'!C"&ROWS($A$1:$A$3))))

I just double-checked the formula and it does work... but you can choose the method you want to use.

There are ways to make a dynamic named range so that you can have blanks in the range and you can add more items as required without having to adjust the range itself in the Named range definition.

See here: http://www.contextures.com/xlnames01.html#Dynamic

Try:

Please Login or Register  to view this content.