Sumif

%3CLINGO-SUB%20id%3D%22lingo-sub-1563754%22%20slang%3D%22en-US%22%3ESumif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563754%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20total%20one%20cell%20from%20multiple%20worksheets%20if%20another%20cell%20has%20a%201%20in%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20formula%20I%20came%20up%20with%3A%26nbsp%3B%26nbsp%3B%3DSUMIF(OGuin%3ATurcotte!D18%2C%221%22%2COGuin%3ATurcotte!B3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20keeps%20giving%20me%20a%20value%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1563754%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563761%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563761%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748357%22%20target%3D%22_blank%22%3E%40KathyG%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESumif()%20does%20not%20work%20as%20a%203D%20formula%20across%20sheets.%20You%20need%20to%20do%20the%20calculation%20on%20each%20sheet%2C%20let's%20say%20in%20cell%20A1%2C%20then%20you%20can%20use%20a%20Sum()%20to%20total%20the%20values%20from%20all%20sheets.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DSUMIF(OGuin%3ATurcotte!A1)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563800%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563800%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1564051%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1564051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748357%22%20target%3D%22_blank%22%3E%40KathyG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20workaround%20could%20be%20as%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2F3d-sumif-for-multiple-worksheets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2F3d-sumif-for-multiple-worksheets%3C%2FA%3E.%20It%20doesn't%20work%20with%20reference%20like%26nbsp%3B%3CSPAN%3EOGuin%3ATurcotte%2C%20with%20named%20range%20of%20all%20sheets%20names.%20But%20if%20the%20structure%20is%20more%20or%20less%20fixed%20that%20shall%20not%20be%20a%20big%20issue.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1564260%22%20slang%3D%22en-US%22%3ERe%3A%20Sumif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1564260%22%20slang%3D%22en-US%22%3Etry%20%3Dsumifs(table1%2Ctable2%2C%22%26gt%3B%3D1%22)%3CBR%20%2F%3E%3CBR%20%2F%3Esample%20video%20here%3CBR%20%2F%3Eyoutu.ve%2F4O9hACKx2o%3C%2FLINGO-BODY%3E
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.