ARRAY FORMULA - coming up with zeros?

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 "".
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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
Back
Top