ARRAY FORMULA - coming up with zeros?

itr674

Well-known Member
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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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)

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

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

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

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.

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?

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 "".

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?

On 2002-09-25 10:14, em wrote:

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?

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.

Replies
3
Views
545
Replies
8
Views
320
Replies
18
Views
446
Replies
2
Views
261
Replies
3
Views
261

1,221,122
Messages
6,158,061
Members
451,461
Latest member
Rayc266

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.

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