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

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.




Thank you so much!  

@Ingeborg Hawighorst 



The workaround could be as here 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.