External reference as a condition
 

I searched this forum and found how to use =SUMPRODUCT instead of
COUNTIF to include more than one condition - great!

Now, instead of entering the condition directly into the function, such
as "Green", I would like to 'point' to a cell that contains the word
"Green". Example, instead of [=SUMPRODUCT((B31:B431="Green"..., write
[=SUMPRODUCT((B31:B431="$X$24"... where X24 contains "Green"

Also, instead of entering a number, such as 15, point to a cell that
contains the number 15. Example, instead of
[=SUMPRODUCT((B31:B43115..., write [=SUMPRODUCT((B31:B431$Y$25...
where Y25 contains 15.

Is this possible? How?

Thanks for your time!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=277829

Hi

in formulas when you use "" it means anything contained between them is a
literal text string ... so by using "$X$24" you're looking for the words
$X$24
so all you need to do is remove the ""

the one will work as you're written it below
=SUMPRODUCT((B31:B431$Y$25) ....

Cheers
JulieD



"Ingeniero1" wrote in message
...

I searched this forum and found how to use =SUMPRODUCT instead of
COUNTIF to include more than one condition - great!

Now, instead of entering the condition directly into the function, such
as "Green", I would like to 'point' to a cell that contains the word
"Green". Example, instead of [=SUMPRODUCT((B31:B431="Green"..., write
[=SUMPRODUCT((B31:B431="$X$24"... where X24 contains "Green"

Also, instead of entering a number, such as 15, point to a cell that
contains the number 15. Example, instead of
[=SUMPRODUCT((B31:B43115..., write [=SUMPRODUCT((B31:B431$Y$25...
where Y25 contains 15.

Is this possible? How?

Thanks for your time!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=277829

You just did it, *except*, take the quotes away from the "$X$24" !
--


Regards,

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

"Ingeniero1" wrote in message
...

I searched this forum and found how to use =SUMPRODUCT instead of
COUNTIF to include more than one condition - great!

Now, instead of entering the condition directly into the function, such
as "Green", I would like to 'point' to a cell that contains the word
"Green". Example, instead of [=SUMPRODUCT((B31:B431="Green"..., write
[=SUMPRODUCT((B31:B431="$X$24"... where X24 contains "Green"

Also, instead of entering a number, such as 15, point to a cell that
contains the number 15. Example, instead of
[=SUMPRODUCT((B31:B43115..., write [=SUMPRODUCT((B31:B431$Y$25...
where Y25 contains 15.

Is this possible? How?

Thanks for your time!

Alex


--
Ingeniero1
------------------------------------------------------------------------
Ingeniero1's Profile:
http://www.excelforum.com/member.php...fo&userid=4029
View this thread: http://www.excelforum.com/showthread...hreadid=277829