# IF Formula

#### singingsister

##### Board Regular
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.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Richard Schollar

##### MrExcel MVP
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

##### Board Regular
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

##### Active Member
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

##### Board Regular

works like a treat! Thanks!

#### singingsister

##### Board Regular
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

##### Active Member

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

#### singingsister

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

#### chicagodiceman

##### Active Member
Because your sheet name contains a space.

ah thanks!!!!

Replies
1
Views
27
Replies
10
Views
277
Replies
2
Views
284
Replies
3
Views
64
Replies
5
Views
114

1,141,613
Messages
5,707,394
Members
421,508
Latest member
Jalayne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back