Add cells from different tabs which contain integers, ignoring text

[Captain Hindsight]

I have a workbook with several tabs. In the front sheet (sheet 1) I want to add up the total of cell A5 from every tab.

Every tab pulls data from the master sheet (Master sheet) using the below formula.

=IF(ISBLANK('Master sheet'!AA5),"",'Master sheet'!AA5)

This is causing the problem as some of the cells contain text.

How can I add without getting an error due to this text.

I don't want to remove the ISBLANK formula because then it shows a 0 every time which looks untidy.

 

[pgc01]

Hi

The function Sum() should ignore the text values.

Can you post the formula that you are using to sum?

 

[Captain Hindsight]

Hi

The function Sum() should ignore the text values.

Can you post the formula that you are using to sum?

=SUM('Tab3'!AA5+'Tab4'!AA5+'Tab5'!AA5)

AA5 in each of those tabs has a formula which returns "" if there is not a number, per my post above

 

[pgc01]

Hi

The syntax is:

=SUM('Tab3'!AA5,'Tab4'!AA5,'Tab5'!AA5)

or you can use a 3D formula

=SUM('Tab3:Tab5'!AA5)

if the worksheets are in sequence in the workbook.

 

[Captain Hindsight]

Hi

The syntax is:

=SUM('Tab3'!AA5,'Tab4'!AA5,'Tab5'!AA5)

or you can use a 3D formula

=SUM('Tab3:Tab5'!AA5)

if the worksheets are in sequence in the workbook.

Great stuff, thank you.

 

[pgc01]

I'm glad it helped. Thaks for the feedback.