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.

Try this, array entered:

=SUM((A1:A30000 = D7)*(B1:B3000038717)*(C1:C30000<E1:E30000))

HTH,
Bernie
MS Excel MVP


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

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.

Keith,
There is no telling if the formula is sound. What I decipher here is
that you want to count the records whe
in column A:A they must be equal to D7,
date in B:B must be after 12/31/2005
and column C:C should be pairwise equal to column E:E

If these were your intentions then the formula is correct. Bernie's
formula is equivalent to what you posted. Are you getting different
results?

HTH
Kostis Vezerides

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.

Hi Epinn,

You are absolutely correct, it can also be done with SP. And I think we
would both agree that it looks simpler and more logical in SP than in
SUM(IF(... <bg.

And you are right, you rarely ever need to use CSE for SP, as it
intrinsically deals with arrays.

And again, you have seen the connection between SUMPRODUCT and SUM(IF( ...
you are really getting this <g.

I did it the way that I did it, not using SUMPRODUCT, and not reducing fully
as Bernie did, as I was trying to help Keith overcome the problems in his
formula. As such, I tried to keep as close as possible to his original
formula so that he would better understand the step-change in the formula
whilst removing all of the fluff. At least, that is my excuse <vbg.

--
HTH

Bob Phillips

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

"Epinn" wrote in message
...
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.

Bob,

Good to hear from you. I felt kind of lonely yesterday talking to myself on the FREQUENCY( ) thread :) but on the other hand, I was so glad that I solved my puzzle all by myself by doing more experiments and using evaluate formula. I have found a way to shrink that formula to 1/3 by using SUM and 1/COUNTIF etc. However, I have some questions. But, haven't decided whether I should post on the other board or here. Any advice as to where? Sorry folks for the distraction.

Back to SUMPRODUCT. Yes, I am a fan of SUMPRODUCT (with * if I may) <g I totally agree with your comments. I don't know if some may think that SUMPRODUCT is more advanced than SUM(IF()). The truth is I find it easier (even for a new user like me) to compose the SUMPRODUCT formula. Using the example on this thread, I probably would have left out ,l in the SUM(IF()) formula. If I had not known about SUMPRODUCT, I would be so lost looking at the SUM formula.

For the record, I entered all *four* formulas on my spreadsheet of 10 records. They appeared to give the same results every time I changed my data. It was fun watching. I hope Keith can give us a feedback once he has found out the culprit. It'll be interesting to know. Earlier, I left out something as minor as a bracket and it was totally off.

You are right, it is easier for me to see the connection between SUM(IF()) and SUMPRODUCT. This is from day one when I started to learn SUMPRODUCT. But I have a problem connecting SUM to SUMPRODUCT. Today is the odd time that I see the two being so close. So, to keep things simple, it is either SUMPRODUCT or SUM(IF()) for me.

I could sense it that you tried to keep your formula as close to Keith's as possible. As a poster, I would appreciate your sensitivity especially when I am trying to figure out what is wrong with my formula.

Hurray! I can hold on to SUMPRODUCT a little bit tighter now; still no complete confidence. I guess practice makes perfect. Thanks a lot for your paper which I have digested a large percentage; I haven't studied the examples at the end.

I apologize to those who don't like length nor distraction.

Until we meet again ......

Epinn

"Bob Phillips" wrote in message ...
Hi Epinn,

You are absolutely correct, it can also be done with SP. And I think we
would both agree that it looks simpler and more logical in SP than in
SUM(IF(... <bg.

And you are right, you rarely ever need to use CSE for SP, as it
intrinsically deals with arrays.

And again, you have seen the connection between SUMPRODUCT and SUM(IF( ....
you are really getting this <g.

I did it the way that I did it, not using SUMPRODUCT, and not reducing fully
as Bernie did, as I was trying to help Keith overcome the problems in his
formula. As such, I tried to keep as close as possible to his original
formula so that he would better understand the step-change in the formula
whilst removing all of the fluff. At least, that is my excuse <vbg.

--
HTH

Bob Phillips

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

"Epinn" wrote in message
...
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.