IF Formula

[singingsister]

I have a spreadsheet with lots of sheets on and I need to do a formula which adds up certain text within each sheet.

IE, I want to total up all the lines with the word "draft" in the cell on all the sheets. I know how to use the "IF" formula when the cells are on the same sheet, but don't know how to apply it over multiple sheets.

 

[Richard Schollar]

Hi SingingSister

Can you be a little more precise? It would be extremely helpful if you list the sheet names and exactly which cells hold the criteria you are looking for and which cells hold the values you want to sum (eg Sheet2!A1 etc). Thanks


Richard

 

[singingsister]

It's the word "draft" on all sheets bar 2 (one is index sheet and one is stats sheet) and it's always in Column C.

The sheet names are names of companies.

 

[chicagodiceman]

In simple terms, you refer to cells on other sheets as Sheet Name followed by exclamation point then the address of the cell. so =Sheet2!A1 will place the value of that cell wherever the formula resides. For your "draft" count it sounds like you'd want to use the countif function.

=countif(Sheet2!A1:L50,"=draft")+countif(Sheet3!A1:L50,"=draft") gives you a count of draft in A1:L50 for two sheets. I now see you've added some info to another post on where this info resides.

 

[singingsister]

works like a treat! Thanks!

 

[singingsister]

one slight problem...

one of the sheets is called A Decisions (supplier name), but when I put this in as the sheet name in the formula, it comes up with a box called "update values: decisions".

Why?

 

[chicagodiceman]

='A Decisions'!E11 will give the value of E11 on that sheet. You need to add the single quote

 

[singingsister]

Thanks. But out of curiosity, why do I have to add the single quote on only some of the sheet names?

 

[chicagodiceman]

Because your sheet name contains a space.

 

[singingsister]

ah thanks!!!!