Nesting Problem
 

thanks to someone on this board earlier on I can create the first part
of this calculation but am unable to join a second criteria within it.
In simple language:

Count number of occurrences where

First Part
Sheet 1, Col A:A is = Sheet 2 Cell 1, and also <= Sheet2 Cell 2,

Second Part (dependent on result of first part)
if Sheet 1 Col P:P is GT= 0, and also LT= 999,999

Where Col A and Cells 1 and 2 are dates and Col P:P is a price range.
S(o that the function finds the number of properties inspected within
any chosen date range, that also then fall between a given price price
range.)

I used SUMProduct for the first part which works fine and I thought I
could do same for the second part and use * to join together but
doesn't work.

Any help greatfully received.

VG


--
vgreen
------------------------------------------------------------------------
vgreen's Profile: http://www.excelforum.com/member.php...o&userid=26510
View this thread: http://www.excelforum.com/showthread...hreadid=398584

=SUMPRODUCT(--(A2:A1000=Sheet2!A1),--(A2:A1000<=Sheet2!A2),--(P2:P1000=0),
--(P2:P1000<=999999))

SUMPRODUCT only works on part of a column, and the ranges must be the same
size

--

HTH

RP
(remove nothere from the email address if mailing direct)


"vgreen" wrote in
message ...

thanks to someone on this board earlier on I can create the first part
of this calculation but am unable to join a second criteria within it.
In simple language:

Count number of occurrences where

First Part
Sheet 1, Col A:A is = Sheet 2 Cell 1, and also <= Sheet2 Cell 2,

Second Part (dependent on result of first part)
if Sheet 1 Col P:P is GT= 0, and also LT= 999,999

Where Col A and Cells 1 and 2 are dates and Col P:P is a price range.
S(o that the function finds the number of properties inspected within
any chosen date range, that also then fall between a given price price
range.)

I used SUMProduct for the first part which works fine and I thought I
could do same for the second part and use * to join together but
doesn't work.

Any help greatfully received.

VG


--
vgreen
------------------------------------------------------------------------
vgreen's Profile:
http://www.excelforum.com/member.php...o&userid=26510
View this thread: http://www.excelforum.com/showthread...hreadid=398584