IF Formula

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Jun 13, 2006
Messages
145
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
Joined
Mar 13, 2004
Messages
371
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
Joined
Jun 13, 2006
Messages
145

ADVERTISEMENT

works like a treat! Thanks!
 

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
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
Joined
Mar 13, 2004
Messages
371

ADVERTISEMENT

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

singingsister

Board Regular
Joined
Jun 13, 2006
Messages
145
Thanks. But out of curiosity, why do I have to add the single quote on only some of the sheet names?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,648
Messages
5,660,105
Members
418,549
Latest member
malmattos

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top