Sumif

Highlighted
New Contributor

I want to total one cell from multiple worksheets if another cell has a 1 in it.  

 

Here is the formula I came up with:  =SUMIF(OGuin:Turcotte!D18,"1",OGuin:Turcotte!B3)

 

It keeps giving me a value error. 

 

Any ideas? 

 

3 Replies
Highlighted

Hello @KathyG ,

 

Sumif() does not work as a 3D formula across sheets. You need to do the calculation on each sheet, let's say in cell A1, then you can use a Sum() to total the values from all sheets.

 

=SUMIF(OGuin:Turcotte!A1)

Highlighted

Thank you so much!  

@Ingeborg Hawighorst 

Highlighted

@KathyG 

The workaround could be as here https://exceljet.net/formula/3d-sumif-for-multiple-worksheets. It doesn't work with reference like OGuin:Turcotte, with named range of all sheets names. But if the structure is more or less fixed that shall not be a big issue.