Array formula returns 0

[davrmars]

My formula is {=SUM(IF(($D$4:$D$100=$D$1)*($B$4:$B$100=$B$1),$G$4:$G$100,0))} it keeps returning a blank cell. The B column has account numbers. The D column has customer numbers and the G column has sales numbers. All are formatted as numbers. No letters, not formatted as text, no dates, auto calculation is on. What other reasons could this not be working?

 

[Jonmo1]

Welcome to the board..

It doesn't matter how the columns are 'formatted' from the format cells dialogue.
It matters what the cells actually contain..
I mean that just because a cell is formatted as a number, doesn't absolutely mean the cell actually contains a number.

So you're probably dealing with Numbers Stored As Text in one or more of the columns..

To tell for sure if your numbers are really numbers or "numbers stored as text", use the ISNUMBER function

=ISNUMBER(G4) and filled down
do this for all cells that are supposed to be numbers.

 

[dms37]

No need for an array formula. Change =SUM(IF( to =SUMPRODUCT( and lose a bracket at the end and just hit enter instead of Ctrl+Shift+Enter

 

[davrmars]

Welcome to the board..

It doesn't matter how the columns are 'formatted' from the format cells dialogue.
It matters what the cells actually contain..
I mean that just because a cell is formatted as a number, doesn't absolutely mean the cell actually contains a number.

So you're probably dealing with Numbers Stored As Text in one or more of the columns..

To tell for sure if your numbers are really numbers or "numbers stored as text", use the ISNUMBER function

=ISNUMBER(G4) and filled down
do this for all cells that are supposed to be numbers.

All cells came back true. All are stored as numbers. No text.

 

[davrmars]

Thank you dms37. I tried sumproduct but it returns #value!

 

[Jonmo1]

Then I can only think of 2 other reasons for that formula to return zero.

1. The answer happens to be zero (you have 2 rows that meet the criteria, 1 ColG = -5 the other ColG = +5, result is zero)
2. There are zero rows that meet both criteria Col D = D1 AND Col B = B1
Check for them to be EXACTLY the same, format the columns to 16 decimal places,
you might find some are 5.2345, but you have it formatted to 2 decimal places so it looks like 5.23

 

[davrmars]

[TABLE="width: 223"]
<tbody>[TR]
[TD]Cust #[/TD]
[TD]Acct[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]6000[/TD]
[TD]77000[/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD]6010[/TD]
[TD]3080[/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD]6020[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]118[/TD]
[TD]6025[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]119[/TD]
[TD]6050[/TD]
[TD]5775[/TD]
[/TR]
[TR]
[TD]120[/TD]
[TD]6075[/TD]
[TD]7700[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]6001[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD]6002[/TD]
[TD]677[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]6003[/TD]
[TD]8000 This is the first nine lines of data. I've even tried rewriting the formula to just give me customer 116 and acct 6010 expecting 3080. I still get blank. Could there be something wrong with the worksheet?[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]

 

[Jonmo1]

It works for me, see formula in E1 copied directly from your original post.
And I put the Acct#s in B, Cust#s in D, Sales in G

Are you entering the formula with CTRL + SHIFT + ENTER?

Excel Workbook
	B	C	D	E	F	G
1	6010		116	3080
2
3	Acct		Cust #			Sales
4	6000		115			77000
5	6010		116			3080
6	6020		117			600
7	6025		118			4000
8	6050		119			5775
9	6075		120			7700
10	6001		121			500
11	6002		122			677
12	6003		123			8000
Sheet2

 

[davrmars]

It works for me, see formula in E1 copied directly from your original post.
And I put the Acct#s in B, Cust#s in D, Sales in G

Are you entering the formula with CTRL + SHIFT + ENTER?

Sheet2

	B	C	D	E	F	G

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]6010[/TD]

[TD="align: right"]116[/TD]
[TD="bgcolor: #FFFF00, align: right"]3080[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: left"]Acct[/TD]

[TD="align: left"]Cust #[/TD]

[TD="align: left"]Sales[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: left"]6000[/TD]

[TD="align: left"]115[/TD]

[TD="align: left"]77000[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: left"]6010[/TD]

[TD="align: left"]116[/TD]

[TD="align: left"]3080[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: left"]6020[/TD]

[TD="align: left"]117[/TD]

[TD="align: left"]600[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: left"]6025[/TD]

[TD="align: left"]118[/TD]

[TD="align: left"]4000[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: left"]6050[/TD]

[TD="align: left"]119[/TD]

[TD="align: left"]5775[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: left"]6075[/TD]

[TD="align: left"]120[/TD]

[TD="align: left"]7700[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: left"]6001[/TD]

[TD="align: left"]121[/TD]

[TD="align: left"]500[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: left"]6002[/TD]

[TD="align: left"]122[/TD]

[TD="align: left"]677[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: left"]6003[/TD]

[TD="align: left"]123[/TD]

[TD="align: left"]8000[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E1 {=SUM(IF(($D$4:$D$100=$D$1)*($B$4:$B$100=$B$1),$G$4:$G$100,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

I am. And I did the same. Opened a new worksheet and put in the formula with just the top 9 data lines and it works. However on the worksheet I have all the data on it doesn't. That's why I think there is something wrong with the worksheet but have no idea what that could be.

 

[Jonmo1]

What does this return..

=INT(D4)=D4

Repeated on ALL the numeric Values.