Keith,

Have you tried to apply your formula to say about 10 records and do evaluate formula and watch the steps? I solved quite a few of my puzzles using evaluate formula. I know you want to use SUM, IF etc. and I respect that. However, I want to talk to my respected teacher, Bob, about SUMPRODUCT for a minute if you don't mind. You can ignore the rest of this post if you like. Sorry for the intrusion.

Bob,

I am so happy because I seem to be able to use SUMPRODUCT to achieve the same result.

=SUMPRODUCT((A1:A30000 = D7)*(B1:B3000038717)*(C1:C30000<E1:E30000))
(No need to enter as an array formula.)

At first I couldn't get my formula working and I thought I forgot about Ctrl+Shift+Enter but then SUMPRODUCT didn't need it. What I missed was the *outside brackets*. What a big difference! I also realize that my formula is very similar to Bernie's. The only difference is for SUM we enter the formula as an array formula whereas for SUMPRODUCT there is no need. I didn't know that SUM alone (i.e. without IF) could be this similar to SUMPRODUCT. Thanks, Bernie.

I feel an urge to say something when I have made a discovery. Thank you for reading.

Epinn

"Bob Phillips" wrote in message ...
In my (limited) testing it seems to work fine, but it can be considerably
simplified

=SUM((IF((A1:A30000=D7)*(B1:B3000038717)*(C1:C300 00<E1:E30000),1)))

still array-entered

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"KR" wrote in message
...
I have a large worksheet and I'm trying to pull some specific information
out of it, and I'm not getting the numbers I expected- so I think there is
something wrong with my syntax. I'm hoping that someone can help point out
my error. As the actual references are quite long, I've shortened them
here
to just show the logic, to determine if the logic itself if bad

={sum((if(A1:A30000 =
D7,1,0))*(if(B1:B3000038717,1,0))*(if(C1:C30000<E 1:E30000,1,0)))}

I had expected this to calculate each if statement to a 1 or 0, them
multiply the outcome of those three if statements (so I'd get a value of 1
if all were true, or zero if any were false), then sum the number of lines
where all three conditions were true.

The actual number I'm getting is somewhere between 2x and 4x the number
I'd
actually expect based on the data, so either my logic is bad, or I have
some
other error. If no-one points out any errors in the above logic
(multiplying
and summing the if statements) then I'll re-post with the actual
(longhand)
formula to see if there is some other problem.

Many thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.