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

 

[itr674]

I chaged the linked cell formula on sheet 2 to read =IF('afw-Basic'!B8,'afw-Basic'!B8,0) and I custom formatted linked cells (same cells with new IF formula) on sheet 2 to [=0]"";General.

Now I get #Value! in those cells? Do I use double quotation marks or single?

 

[Aladin Akyurek]

On 2002-09-25 11:14, em wrote:
I chaged the linked cell formula on sheet 2 to read =IF('afw-Basic'!B8,'afw-Basic'!B8,0) and I custom formatted linked cells (same cells with new IF formula) on sheet 2 to [=0]"";General.

Now I get #Value! in those cells? Do I use double quotation marks or single?

That's the right format...

Examine the following closely and try to figure out whether your situation differs from it...

aaExample em.xls
	A	B	C	D	E	F	G	H	I
1	a	q	2		3		7		a
2	a	w	3		2		4		q
3	b	w	4		7				29
4	a	q	5		4		8
5	c	w	6		1		2
Sheet1

 

[itr674]

The data appears to be like it.

 

[Aladin Akyurek]

On 2002-09-25 11:56, em wrote:
The data appears to be like it.

Where does the data come from -- the web?

 

[itr674]

No, it comes from another sheet within a workbook.

I just formatted all the cells in the columns that are to be summed so a zero would be there instead of "" if it where empty--that did not work.

I don't understand why it will SUM one column when only that column is in the formula, but as soon as you add another column I get the value error.

I just experimented with each column seperately to see if the problem would show up for a particular column - all worked perfectly as long as they are there by them selves. But as soon as I add that second column it will not work???

I have been working on this since 0100 this morning and am about to fall out. I was thinking later tonight or tomorrow I would try to put together a duplicate workbood and maybe send it to you would have time to look at it--any my day is over for now ...

 

[itr674]

Aladin - I went back and looked a formula I had posted earlier today and noticed that I have a comma seperating parts of the formulas.

Is the comma correct or should it be a * and are spaces between the + signs OK ?


=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 19:11

 

[Aladin Akyurek]

On 2002-09-25 12:58, em wrote:
Aladin - I went back and looked a formula I had posted earlier today and noticed that I have a comma seperating parts of the formulas.

Is the comma correct or should it be a * and are spaces between the + signs OK ?


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

The comma is correct. Spaces around + is no problem.

 

[Aladin Akyurek]

I examined your spreadsheet: You did not fully carry out what I suggested in earlier replies:

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.

See the original post [from which the above is quoted] why this needs to be done.

 

[itr674]

Aladin - I mis understood "linked" cells. I was putting the formula you suggested in the cells that were linked and not the cells that I was getting the data from.

I went back and completed your instructions and they worked.

THANKS ...

 

[itr674]

Aladin - I mis understood "linked" cells. I was putting the formula you suggested in the cells that were linked and not the cells that I was getting the data from.

I went back and completed your instructions and they worked.

THANKS ...