# Blank cells being read as 0

#### Shodi

##### Board Regular
Hello everyone, I need help with a template I have created.
The template in of 2 sheets where Sheet 2 reflects data manually inserted in Sheet 1.

Eg: Cell A1 in Sheet 2 has the formula =IF('Sheet1'!A1="","",'Sheet1'!A1),
Cell B1 in Sheet 2 has the formula =IF('Sheet1'!B1="","",'Sheet1'!B1) and so on.

Now the data from Sheet 1 fills 145 rows till column AB in Sheet 2.
In column AC in Sheet 2, I have a SUMIF formula that calculates the values row wise in Sheet 2 itself.
The issue I face is that the SUMIF formula in AC is reading the blank cells in Sheet 2 as 0 and hence the output is inaccurate.
Currently I am having to copy paste as values (removing the formulas) the data in Sheet 2 and then insert the SUMIF formula in column AC. This actually beats the purpose of a template with linked sheets.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### BarryL

##### Well-known Member
If its adding 0 how does that effect a SUMIF?

#### Shodi

##### Board Regular
Dear Barry, the SUMIF formula is calculating the sum of constants in row A1 to AB1 based on the values in the rows below

EG:
in cell AC2 the formula I have is =SUMIF(A2:AB2,"<>",\$A\$1:\$AB\$1)
in cell AC3 the formula I have is =SUMIF(A3:AB3,"<>",\$A\$1:\$AB\$1) and so on.

When this formula reads a blank cell as 0 the resulting output includes the values in A1 to AB1 that should not be considered as the corresponding value below is blank.
To make this clear please find a sample data below.

 A B C D E F G H Row 1 Constant 2 2 2 4 3 5 18 Row 2 DATA 1 2 2 3 3 0 18 Row 3 DATA 2 1 2 2 2 3 1 18 Row 4 DATA 3 2 1 3 18 Row 5 DATA 4 2 1 2 4 5 18 Row 6 DATA 5 2 1 2 3 5 18

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

This is how the SUMIF formula is behaving now because it has read the presence of the formulae in cells C2, B4, E4, G4, F5 and D6 as 0 and is returning the sum of all the values from B1 to G1.
Ideally the results appearing in column H is, H2=16 (C2 is blank hence 18-2), H3=18 (NO BLANK CELLS), H4=7 (B3, E3 and G3 are blank hence 18-11) and so on. Hope this is clear. Please help.

#### BarryL

##### Well-known Member
do you mean?

=SUMIF(A3:AB3,"<>""",\$A\$1:\$AB\$1)
or
=SUMIF(A3:AB3,"<>"&"",\$A\$1:\$AB\$1)

#### Special-K99

##### Well-known Member
What does this result in?

=LEN(C2)
=CODE(C2)

Is this website related in which case the blanks could be CHAR(160)

#### Shodi

##### Board Regular
This is not website related, it is purely data in excel.

The formula I am using currently is =SUMIF(A3:AB3,"<>",\$A\$1:\$AB\$1).

This is meant to return the sum of values from A1 to AB1 where the corresponding cells in the rows below have numeric values meaning if a cell in a row is blank then the result will not include the corresponding value in A1:AB1 in the summed output.

#### Special-K99

##### Well-known Member
I didnt think you could put a range of test values in a SUMIF.
If that is the case then you'll have to use SUMPRODUCT.
or perhaps SUM(IF(...))

For clarity still no answer on
=LEN(C2)
=CODE(C2)

???

Last edited:

#### Shodi

##### Board Regular
The SUMIF formula is giving be the desired result. The only glitch is the bank cells being read a 0.

C2 has the formula
=IF('Sheet1'!C2="","",'Sheet1'!C2),

=LEN(C2) gives "0".

=CODE(C2) gives "#VALUE".

Is there a way around this? Please!!

#### FormR

##### MrExcel MVP
in cell AC2 the formula I have is =SUMIF(A2:AB2,"<>",\$A\$1:\$AB\$1)

Hi, here is an alternative formula that you can try:

=SUMPRODUCT(--(LEN(A2:AB2)>0),\$A\$1:\$AB\$1)

#### Shodi

##### Board Regular
Dear MrExcel, thanks a lot, this SUMPRODUCT formula is not reading the blank cells even though the cells have a formula in them.

Would you also please be kind enough to explain this formula as I am not able to understand how this works??

Replies
2
Views
142
Replies
5
Views
199
Replies
6
Views
404
Replies
1
Views
125
Replies
7
Views
222

1,190,898
Messages
5,983,467
Members
439,843
Latest member
PlanetFitness

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