Sum(if array isn't working..

T

Tornados

=SUM((ISNUMBER(B3:B416))*(DATE(RIGHT((date!B3:B416),4),MID((date!B3:B416),4,2),LEFT((date!B3:B416),2))=(TODAY()-1))*(B3:B416))

Hello, the above formula is my latest try to sum a certain rang
(b3:b416) which has to be a number, while the corresponding cell i
sheet date! has to have a date equalling yesterday. Unfortunately th
date! sheet does not have the date in the right format.... and i don'
want to add another sheet...

While the complete date() function provides a true array, when i add
sum function in the array it gives me #value...

i tried the sum(if()) array notation also but it just is not working.
I can imagine that the date function is just too much but can someon
please confirm this?

Thanks. Tornados.....
 
J

JE McGimpsey

I read pretty carefully the first time, and it works fine in my test
workbook

ftp://ftp.mcgimpsey.com/excel/tornados_demo.xls

Perhaps you don't actually have actual dates in your date sheet? The
format doesn't matter - worksheet functions operate on the value stored
in the cell, not on how they're displayed.

Did you try the formula I gave you? What "doesn't work"? wrong value? no
value? error message? crash?
 
D

Domenic

Hi,

The formula looks fine and, in fact, I just tested it myself and had no
problems.

However, make sure that you enter the formula using CTRL+SHIFT+ENTER.

Hope this helps!
 
P

Peo Sjoblom

If you are talking about J.E. McGimpsey's formula it isn't necessary to
array enter
it

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
D

Domenic

Hi Peo,

Actually, I was talking about Tornados original formula. Sorry, I
should have been specific.

Thanks for clearing that up!
 
T

Tornados

Sorry for the late reply!

Tx for your help but again when you read my first post you'll see i'
converting the value (which isn't a date number) with the dat
function. You basically ignored that lol.. .and the problem i
unfortunately because of this .......

Cheers.
 
Top