Sum of one cell over multiple worksheets if another cell from multiple work sheets =1

[vinniev19]

I am creating a summary page and would like a formula that enters a Sum of N36 across an entire workbook if C23=1.

For example if I have a multiple worksheets I'd like B3 in my summary to add together the totals of N36 in worksheets where C23=1 and not in worksheets where C23 is blank.

excelCapture.PNG

[Special-K]

Google for "3d formulas" in Excel

http://excel3dreferencing.blogspot.co.uk/

[Fotis1991]

Hi and welcome to the forum.

Create a list of your Sheets Names and name it as List.

Then in B3, use this formula.

=SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!C23"),1,INDIRECT("'"&List&"'!N36")))

Is this, works for you?

[vinniev19]

Hi and welcome to the forum.

Create a list of your Sheets Names and name it as List.

Then in B3, use this formula.

=SUMPRODUCT(SUMIF(INDIRECT("'"&List&"'!C23"),1,INDIRECT("'"&List&"'!N36")))

Is this, works for you?

Hi, thanks for your reply. Is there any way I could replace the list with a Range. I have a blank form at each end of a range of sheets which I use as start and end sheets in formulas for example =SUM(ROSSSTART:ROSSEND!E23) Which allows me insert worksheets in-between them calculate the Sum.

[Fotis1991]

We can do this for SUM formula, but really i do't know if we can do somrthing like this, using SUMIF...

[vinniev19]

When you say create list what do you mean?

[Fotis1991]

Define Names?(Inser>Name>>Define) Is this "saying" something to you? Sorry i am not able to explain this better.

See if my sample, helps you

[vinniev19]

Yes it does thanks very much.

[Fotis1991]

You are welcome

Pls, mark your Thread, as Solved.