ARRAY FORMULA - coming up with zeros?

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

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

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top