I have a table of data with a date column and an acreage column. I want to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start counting
(named it DOS1) the used the follow formula: countif(Data!f2:f600,"&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those sales of
less than 1 acre listed in Data!E:E??? I using Excel 2002.
--
LarryJasper

Try this:

Assumes no empty cells in your acreage column.

=SUMPRODUCT(--(Data!E2:E600<1),--(Data!F2:F600DOS1))

--
Biff
Microsoft Excel MVP


"Larry Smith" wrote in message
...
I have a table of data with a date column and an acreage column. I want to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start counting
(named it DOS1) the used the follow formula:
countif(Data!f2:f600,"&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those sales
of
less than 1 acre listed in Data!E:E??? I using Excel 2002.
--
LarryJasper

I have a table of data with a date column and an acreage column. I want to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start counting
(named it DOS1) the used the follow formula:
countif(Data!f2:f600,"&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those sales
of
less than 1 acre listed in Data!E:E??? I using Excel 2002.

Not sure what the was about... so I assumed you meant = in the following
formula (which I believe does what you want):

=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Rick

Sat, 14 Jul 2007 13:12:00 -0700 from Larry Smith
:
I have a table of data with a date column and an acreage column. I want to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start counting
(named it DOS1) the used the follow formula: countif(Data!f2:f600,"&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those sales of
less than 1 acre listed in Data!E:E??? I using Excel 2002.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

--
LarryJasper


"Rick Rothstein (MVP - VB)" wrote:

I have a table of data with a date column and an acreage column. I want to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start counting
(named it DOS1) the used the follow formula:
countif(Data!f2:f600,""&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those sales
of
less than 1 acre listed in Data!E:E??? I am using Excel 2002.

Not sure what the was about... so I assumed you meant = in the following
formula (which I believe does what you want):

=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Rick

Why would you Multiply these 2 numbers?? I want just a count meeting both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both condition must be met.

=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Why would you Multiply these 2 numbers?? I want just a count meeting
both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both
condition must be met.

That is one of the syntaxes for the SUMPRODUCT function. What is happening
is the first expression is multiplied by the second for each element in the
range and then those individual products are summed up (hence the name
SUMPRODUCT). Now look at the expressions... they are each logical
expressions which evaluate to TRUE or FALSE. (Excel automatically converts
TRUE to 1 and FALSE to 0 when used in a mathematical computation.) So, if
both conditions are met, you have a product of 1 and if any one condition is
not met, you have a product of 0. That means the sum will only increment by
1 for each product where both conditions are met. The end result of that...
the count that you asked for.

The other way I could have written the formula is this way....

=SUMPRODUCT(--(Data!F2:F600=DOS1),--(Data!E2:E600<1))

but now you have to convert the TRUE and FALSE results to numbers manually.
This is done with the double unary symbols... my personal preference is to
avoid the double unary symbol if I can (hence, why I chose the
multiplication form of the formula over the comma form).

Rick

"Larry Smith" wrote in message
...

--
LarryJasper


"Rick Rothstein (MVP - VB)" wrote:

I have a table of data with a date column and an acreage column. I want
to
count how many parcels of less than 1 acre have sold within the past 6
months. I set a field with the date from which I want to start
counting
(named it DOS1) the used the follow formula:
countif(Data!f2:f600,""&DOS1).
That returns the number of sales after the DOS1 date to current date.
Question is this: how do I then modify the formula to get only those
sales
of
less than 1 acre listed in Data!E:E??? I am using Excel 2002.

Not sure what the was about... so I assumed you meant = in the
following
formula (which I believe does what you want):

=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Rick

Why would you Multiply these 2 numbers?? I want just a count meeting
both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both
condition must be met.

The formula *is* counting.

Let's assume your data is like this:

DOS1 = 3

...........E..........F
1......1.0.........4
2......0.5.........4
3......0.8.........2
4......1.5.........4
5......0.9.........3

=SUMPRODUCT((F1:F5=DOS1)*(E1:E5<1))

The result is 2

Each of these expressions will return an array of either TRUE or FALSE:

(F1:F5=DOS1)
(E1:E5<1)

Multiplying these 2 arrays together will then return an array of either 1 or
0

T * F = 0
T * T = 1
F * T = 0
T * F = 0
T * T = 1

This array of 1 or 0 is then totaled by the SUMPRODUCT function as the
result: 2

See the link provided by Stan for a detailed explanation of SUMPRODUCT.

--
Biff
Microsoft Excel MVP

--
LarryJasper


"Rick Rothstein (MVP - VB)" wrote:

=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Why would you Multiply these 2 numbers?? I want just a count meeting
both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both
condition must be met.

That is one of the syntaxes for the SUMPRODUCT function. What is happening
is the first expression is multiplied by the second for each element in the
range and then those individual products are summed up (hence the name
SUMPRODUCT). Now look at the expressions... they are each logical
expressions which evaluate to TRUE or FALSE. (Excel automatically converts
TRUE to 1 and FALSE to 0 when used in a mathematical computation.) So, if
both conditions are met, you have a product of 1 and if any one condition is
not met, you have a product of 0. That means the sum will only increment by
1 for each product where both conditions are met. The end result of that...
the count that you asked for.

The other way I could have written the formula is this way....

=SUMPRODUCT(--(Data!F2:F600=DOS1),--(Data!E2:E600<1))

but now you have to convert the TRUE and FALSE results to numbers manually.
This is done with the double unary symbols... my personal preference is to
avoid the double unary symbol if I can (hence, why I chose the
multiplication form of the formula over the comma form).

Rick

Thank for the lesson. and the formula. It works.

"Rick Rothstein (MVP - VB)" wrote in
message ...
=SUMPRODUCT((Data!F2:F600=DOS1)*(Data!E2:E600<1))

Why would you Multiply these 2 numbers?? I want just a count meeting
both conditions 1)Greater than DOS1 2)Less than 1 (acre). Both
condition must be met.

That is one of the syntaxes for the SUMPRODUCT function. What is happening
is the first expression is multiplied by the second for each element in
the range and then those individual products are summed up (hence the name
SUMPRODUCT). Now look at the expressions... they are each logical
expressions which evaluate to TRUE or FALSE. (Excel automatically converts
TRUE to 1 and FALSE to 0 when used in a mathematical computation.) So, if
both conditions are met, you have a product of 1 and if any one condition
is not met, you have a product of 0. That means the sum will only
increment by 1 for each product where both conditions are met. The end
result of that... the count that you asked for.

The other way I could have written the formula is this way....

=SUMPRODUCT(--(Data!F2:F600=DOS1),--(Data!E2:E600<1))

but now you have to convert the TRUE and FALSE results to numbers
manually. This is done with the double unary symbols... my personal
preference is to avoid the double unary symbol if I can (hence, why I
chose the multiplication form of the formula over the comma form).

Rick

SUMPRODUCT: * vs --

http://tinyurl.com/2norxe

--
Biff
Microsoft Excel MVP

SUMPRODUCT: * vs --

http://tinyurl.com/2norxe

I found the timings at this link

http://img46.imageshack.us/img46/4064/calctimesax7.jpg

interesting. But what are the time measurements in... seconds? Assuming so,
should one really be overly concerned about a process that takes a little
over a hundredth of a second as compared to one taking about three-quarters
of a hundredth of a second? I doubt too many users would be able to perceive
a real-world delay of an extra three or four thousandth of a second.

Rick

SUMPRODUCT: * vs --

http://tinyurl.com/2norxe

I found the timings at this link

http://img46.imageshack.us/img46/4064/calctimesax7.jpg

interesting. But what are the time measurements in... seconds? Assuming
so, should one really be overly concerned about a process that takes a
little over a hundredth of a second as compared to one taking about
three-quarters of a hundredth of a second? I doubt too many users would be
able to perceive a real-world delay of an extra three or four thousandth
of a second.

In re-reading my posting, I think if may sound more confrontational
(arrogant?) than I meant it too... I was just wondering aloud if the timing
difference is really something that should be of concern in a real-world
application. One process could be twice (even three, four, or more times) as
fast as another and yet, in real-world terms, be of no significance
what-so-ever. I think this issue may be just such a situation.

Rick

How many posts have you seen here about slow calculation?

I agree that in the majority of applications there is no real perceivable
difference but in "heavy duty" applications every bit of efficiency helps no
matter where you find it.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
SUMPRODUCT: * vs --

http://tinyurl.com/2norxe

I found the timings at this link

http://img46.imageshack.us/img46/4064/calctimesax7.jpg

interesting. But what are the time measurements in... seconds? Assuming
so, should one really be overly concerned about a process that takes a
little over a hundredth of a second as compared to one taking about
three-quarters of a hundredth of a second? I doubt too many users would
be able to perceive a real-world delay of an extra three or four
thousandth of a second.

In re-reading my posting, I think if may sound more confrontational
(arrogant?) than I meant it too... I was just wondering aloud if the
timing difference is really something that should be of concern in a
real-world application. One process could be twice (even three, four, or
more times) as fast as another and yet, in real-world terms, be of no
significance what-so-ever. I think this issue may be just such a
situation.

Rick

I agree that in the majority of applications there is no real perceivable
difference but in "heavy duty" applications every bit of efficiency helps
no matter where you find it.

I guess my short-sightedness stems from my Excel background... approximately
15-year break from Excel and, back when I did work with it, my applications
were all Engineering ones, so I doubt any of them rose anywhere near the
level you are suggesting with the words "heavy duty". I guess if you have a
few thousand or so of those SUMPRODUCTs strewn about your application, the
few thousandths of a second mount up. I hate you, you know... because of
you, I am going to have to switch from the multiplication syntax (which I
really, really like better) to the comma version... I hate you.<vbg

Rick

I'd just like to put in my 2¢, since I almost always use the asterisk form
of SP due to the way it's used in many of the WBs at our plants.

Depending on the means of data input, it might prove wiser to evaluate your
different usage's before universally changing to the unary form.

A couple of years ago I did what you're thinking of doing, and got *burned*,
due to my lack of properly evaluating how my formulas were being used in the
different departments.

Be advised, that the asterisk form will compute all numbers, in whatever
format they're presented, and will return errors if there are anything
*other* then numbers in the calculating range.
The unary form will simply "go by" these "non-numbers", calculating and
returning values, with *no* warning (error messages), advising that
something might be amiss.

I described my misfortune in this old thread:

http://tinyurl.com/2e4equ

If you're sure that your values are consistent, and their input is beyond
reproach, by all means, switch your formulas.
But with users like mine, I feel much safer using the asterisk!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Rick Rothstein (MVP - VB)" wrote in
message ...
I agree that in the majority of applications there is no real
perceivable
difference but in "heavy duty" applications every bit of efficiency
helps
no matter where you find it.

I guess my short-sightedness stems from my Excel background...
approximately
15-year break from Excel and, back when I did work with it, my
applications
were all Engineering ones, so I doubt any of them rose anywhere near the
level you are suggesting with the words "heavy duty". I guess if you have
a
few thousand or so of those SUMPRODUCTs strewn about your application, the
few thousandths of a second mount up. I hate you, you know... because of
you, I am going to have to switch from the multiplication syntax (which I
really, really like better) to the comma version... I hate you.<vbg

Rick

Thanks for posting that. It looks like it is back to the drawing boards for
me.<g

As I have mentioned in some of my other postings, I am newly returned to
Excel after a 10-year plus absence. Hell, I don't even remember if they had
SUMPRODUCT in Excel back then.<g Anyway, I don't remember reading about
what you have pointed out when I reacquainted myself with Excel a couple or
so months ago; so obviously I will have to study this issue some more.
Perhaps if some of the other regulars here would join this discussion, it
might be helpful.

Thanks again for your post. Much appreciated.

Rick



"Ragdyer" wrote in message
...
I'd just like to put in my 2¢, since I almost always use the asterisk form
of SP due to the way it's used in many of the WBs at our plants.

Depending on the means of data input, it might prove wiser to evaluate
your
different usage's before universally changing to the unary form.

A couple of years ago I did what you're thinking of doing, and got
*burned*,
due to my lack of properly evaluating how my formulas were being used in
the
different departments.

Be advised, that the asterisk form will compute all numbers, in whatever
format they're presented, and will return errors if there are anything
*other* then numbers in the calculating range.
The unary form will simply "go by" these "non-numbers", calculating and
returning values, with *no* warning (error messages), advising that
something might be amiss.

I described my misfortune in this old thread:

http://tinyurl.com/2e4equ

If you're sure that your values are consistent, and their input is beyond
reproach, by all means, switch your formulas.
But with users like mine, I feel much safer using the asterisk!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Rick Rothstein (MVP - VB)" wrote in
message ...
I agree that in the majority of applications there is no real
perceivable
difference but in "heavy duty" applications every bit of efficiency
helps
no matter where you find it.

I guess my short-sightedness stems from my Excel background...
approximately
15-year break from Excel and, back when I did work with it, my
applications
were all Engineering ones, so I doubt any of them rose anywhere near the
level you are suggesting with the words "heavy duty". I guess if you have
a
few thousand or so of those SUMPRODUCTs strewn about your application,
the
few thousandths of a second mount up. I hate you, you know... because of
you, I am going to have to switch from the multiplication syntax (which I
really, really like better) to the comma version... I hate you.<vbg

Rick