I keep getting a #value error and i'm not sure why?
=SUM(IF(Capitol!$B$13:$B$15=$L$4,IF(Capitol!$C$13: $C$15=$L$5,IF(Capitol!$D$13:$D$15=$L$6,IF(Capitol! $A$13:$A$15,1,0)))))

Thanks for any help

This looks like an array formula.

F2 then CTRL + SHIFT + ENTER

Excel will put { } around it.


Gord Dibben MS Excel MVP

On Sun, 27 Jul 2008 12:08:59 -0700, kp0250
wrote:

I keep getting a #value error and i'm not sure why?
=SUM(IF(Capitol!$B$13:$B$15=$L$4,IF(Capitol!$C$13 :$C$15=$L$5,IF(Capitol!$D$13:$D$15=$L$6,IF(Capitol !$A$13:$A$15,1,0)))))

Thanks for any help

In addition...

What are you attempting to do with the last IF:

....IF(Capitol!$A$13:$A$15,1,0)...

If there are any TEXT entries in that range you'll also get the #VALUE!
error.

You can do this with a normally entered formula. The "blank area" at the end
is for whatever IF(Capitol!$A$13:$A$15 is supposed to do/mean.

=SUMPRODUCT(--(Capitol!$B$13:$B$15=$L$4),--(Capitol!$C$13:$C$15=$L$5),--(Capitol!$D$13:$D$15=$L$6),--(............))


--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
This looks like an array formula.

F2 then CTRL + SHIFT + ENTER

Excel will put { } around it.


Gord Dibben MS Excel MVP

On Sun, 27 Jul 2008 12:08:59 -0700, kp0250
wrote:

I keep getting a #value error and i'm not sure why?
=SUM(IF(Capitol!$B$13:$B$15=$L$4,IF(Capitol!$C$1 3:$C$15=$L$5,IF(Capitol!$D$13:$D$15=$L$6,IF(Capito l!$A$13:$A$15,1,0)))))

Thanks for any help

That formula needs "array entering", i.e. confirmed with CTRL+SHIFT+ENTER so
that curly braces appear around the formula, have you done that?

"kp0250" wrote:

I keep getting a #value error and i'm not sure why?
=SUM(IF(Capitol!$B$13:$B$15=$L$4,IF(Capitol!$C$13: $C$15=$L$5,IF(Capitol!$D$13:$D$15=$L$6,IF(Capitol! $A$13:$A$15,1,0)))))

Thanks for any help

I have now... lol

It was such an easy fix, but i didn't realize it. Thank you.

"daddylonglegs" wrote:

That formula needs "array entering", i.e. confirmed with CTRL+SHIFT+ENTER so
that curly braces appear around the formula, have you done that?

"kp0250" wrote:

I keep getting a #value error and i'm not sure why?
=SUM(IF(Capitol!$B$13:$B$15=$L$4,IF(Capitol!$C$13: $C$15=$L$5,IF(Capitol!$D$13:$D$15=$L$6,IF(Capitol! $A$13:$A$15,1,0)))))

Thanks for any help