ARRAY FORMULA - coming up with zeros?

[itr674]

Have the following array formua in sheet. It comes up with zero when there is data that should sum??

SUM(IF(('test'!$B$9:$B$108="6K*")*('test'!$E$9:$E$108="123test"),'test'!$K$9:$K$108))
This message was edited by on 2002-10-18 09:37
This message was edited by em on 2003-01-31 23:47

 

[Ian Mac]

On 2002-09-25 05:34, em wrote:
Have the following array formua in sheet. It comes up with zero when there is data that should sum??

SUM(IF(('test'!$B$9:$B$108="6K*")*('test'!$E$9:$E$108="123test"),'test'!$K$9:$K$108))

You can't use a wild card as you have, you need to use:

=SUM(IF((LEFT($B$9:$B$108,2)="6K")*($E$9:$E$108="123test"),$K$9:$K$108))

or non array entered:

=SUMPRODUCT((LEFT($B$9:$B$108,2)="6K")*($E$9:$E$108="123test"),$K$9:$K$108)

 

[itr674]

I thought the " * " might be the problem.

Does it (*) act as some kind of joiner?

Here is another problem--I keep getting the Value error.

I the first two criteria are met I need to add 3 columns.

=SUMPRODUCT((LEFT('sheetname'!$B$9:$B$108,2)="3Y")*(sheetname'!$E$9:$E$108="test"),'sheetname'!$AG$9:$AG$108+sheetname'!$AJ$9:$AJ$108+'sheetname'!$AM$9:$AM$108)
This message was edited by em on 2002-09-25 06:25

 

[Ian Mac]

On 2002-09-25 06:22, em wrote:
I thought the " * " might be the problem.

Does it (*) act as some kind of joiner?

Here is another problem--I keep getting the Value error.

I the first two criteria are met I need to add 3 columns.

=SUMPRODUCT((LEFT('sheetname'!$B$9:$B$108,2)="3Y")*(sheetname'!$E$9:$E$108="test"),'sheetname'!$AG$9:$AG$108+sheetname'!$AJ$9:$AJ$108+'sheetname'!$AM$9:$AM$108)
This message was edited by em on 2002-09-25 06:25

I believe I've answered you with:

http://www.mrexcel.com/board/viewtopic.php?topic=23265&forum=2

 

[itr674]

I tried this after looking at your other post and all has worked so far except this one, I get a #value error--so I figure I have something out of place?

Is the problem coming from me not using the "sheet name" correctly in the formula?

SUMPRODUCT((LEFT(sheetname'!$B$9:$B$108,2)="3Y")*('sheetname'!$E$9:$E$108="test"),(sheetname'!$AG$9:$AG$108 + sheetname'!$AJ$9:$AJ$108 + sheetname'!$AM$9:$AM$108))

 

[Ian Mac]

On 2002-09-25 06:48, em wrote:
I tried this after looking at your other post and all has worked so far except this one, I get a #value error--so I figure I have something out of place?

Is the problem coming from me not using the "sheet name" correctly in the formula?

SUMPRODUCT((LEFT(sheetname'!$B$9:$B$108,2)="3Y")*('sheetname'!$E$9:$E$108="test"),(sheetname'!$AG$9:$AG$108 + sheetname'!$AJ$9:$AJ$108 + sheetname'!$AM$9:$AM$108))

There should be an apostrophy before all the sheet names, also, a minor point.
You should include the = in the front of your formulas when placing them on the board.
This my own opinion, as I sometime wonder if people have missed a vital '(' or some other such thing from there formulas.

 

[itr674]

Ian Mac - roger on the putting the = sign with posts, and thanks for the help this morning,

I checked, and I do have the apostrope at the beginning and end of sheet name but still getting the #Value! error?

 

[Aladin Akyurek]

On 2002-09-25 08:33, em wrote:
Ian Mac - roger on the putting the = sign with posts, and thanks for the help this morning,

I checked, and I do have the apostrope at the beginning and end of sheet name but still getting the #Value! error?

I bet the ranges that are OR'ed (+'ed) contain formula-generated "". Change those formulas to return 0 instead of a "".

 

[itr674]

Aladin - you are correct.

I have one sheet (sheet 1) that we do all manual inputs to columns A thru J.

Those cells are linked to sheet 2 columns A thru J.

Then sheet 3 is the summary sheet. The summary sheet (sheet 3) uses 2 columns linked from sheet 2.

The funny thing is that the formula below works--the only difference in it and the one that doesn't work is that it only sums 1 column instead of 3?

So even though the first statements in the formula are using formualated/linked cells it works--its just the that multiple summing of columns that meet the criteria are not working in the other formula?

=SUMPRODUCT((LEFT('test2'!$B$9:$B$108,2)="3Y")*('test2'!$E$9:$E$108="test"),'test2'!$AD$9:$AD$108)

 

[Aladin Akyurek]

On 2002-09-25 10:14, em wrote:
Aladin - you are correct.

I have one sheet (sheet 1) that we do all manual inputs to columns A thru J.

Those cells are linked to sheet 2 columns A thru J.

Then sheet 3 is the summary sheet. The summary sheet (sheet 3) uses 2 columns linked from sheet 2.

The funny thing is that the formula below works--the only difference in it and the one that doesn't work is that it only sums 1 column instead of 3?

So even though the first statements in the formula are using formualated/linked cells it works--its just the that multiple summing of columns that meet the criteria are not working in the other formula?

=SUMPRODUCT((LEFT('test2'!$B$9:$B$108,2)="3Y")*('test2'!$E$9:$E$108="test"),'test2'!$AD$9:$AD$108)

There is nothing here of a surprise.

The comma that separates the range to sum from the condition tests takes care of the text values in the range to sum. Change it * and you'll get a #VALUE! error because you will be multiplying a text value with a 1 or a 0. The longer formula is trying to add (or to OR) a number with a text value, hence the error value.

Change the linking formulas according the following logic:

=IF(Sheet1!A1,Sheet1!A1,0)

Thus a 0, not a "".

Select such cells and custom format them as:

[=0]"";General

The longer formula Ian suggested will work as intended.