Sum(if array isn't working..

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

 

[JE McGimpsey]

One way:

=SUMPRODUCT(B3:B416,--(date!B3:B416=(TODAY()-1)))

 

[Tornados]

? No that will not be working i'm afraid.. please read my questio
carefully..

 

[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?

 

[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!

 

[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

 

[Domenic]

Hi Peo,

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

Thanks for clearing that up!

 

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