Blank cells being read as 0

[Shodi]

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.
Please help me with a way around this. Thanks in advance.

 

[BarryL]

If its adding 0 how does that effect a SUMIF?

 

[Shodi]

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]

do you mean?

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

 

[Special-K99]

What does this result in?

=LEN(C2)
=CODE(C2)

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

 

[Shodi]

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]

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)

???

 

[Shodi]

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]

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]

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