SUMPRODUCT column criteria
 

Can anyone tell me if an entire column can be used as criteria in a
SUMPRODUCT formula? I tried: =-SUMPRODUCT(--('PS Sales'!$K:$K='Jan
09'!I$1),--('PS Sales'!$F:$F='Jan 09'!$A18),'PS Sales'!$H:$H) but got a #NUM!
error.

Using =-SUMPRODUCT(--('PS Sales'!$K$1:$K$60000='Jan 09'!I$1),--('PS
Sales'!$F$1:$F$60000='Jan 09'!$A18),'PS Sales'!$H$1:$H$60000) works fine, but
typing the row numbers every time is a huge waste of time!

Thanks!
--
GD

SUMPRODUCT column criteria
 

Hi,
Selecting the entire column will work only with excel 2007

"GD" wrote:

Can anyone tell me if an entire column can be used as criteria in a
SUMPRODUCT formula? I tried: =-SUMPRODUCT(--('PS Sales'!$K:$K='Jan
09'!I$1),--('PS Sales'!$F:$F='Jan 09'!$A18),'PS Sales'!$H:$H) but got a #NUM!
error.

Using =-SUMPRODUCT(--('PS Sales'!$K$1:$K$60000='Jan 09'!I$1),--('PS
Sales'!$F$1:$F$60000='Jan 09'!$A18),'PS Sales'!$H$1:$H$60000) works fine, but
typing the row numbers every time is a huge waste of time!

Thanks!
--
GD

SUMPRODUCT column criteria
 

UGH!!!

Thanks, Eduardo!
--
GD


"Eduardo" wrote:

Hi,
Selecting the entire column will work only with excel 2007

"GD" wrote:

Can anyone tell me if an entire column can be used as criteria in a
SUMPRODUCT formula? I tried: =-SUMPRODUCT(--('PS Sales'!$K:$K='Jan
09'!I$1),--('PS Sales'!$F:$F='Jan 09'!$A18),'PS Sales'!$H:$H) but got a #NUM!
error.

Using =-SUMPRODUCT(--('PS Sales'!$K$1:$K$60000='Jan 09'!I$1),--('PS
Sales'!$F$1:$F$60000='Jan 09'!$A18),'PS Sales'!$H$1:$H$60000) works fine, but
typing the row numbers every time is a huge waste of time!

Thanks!
--
GD

SUMPRODUCT column criteria
 

You can use a named range if that would help - but as far as I am aware the
range cannot be $A$:$A$

edvwvw

GD wrote:
Can anyone tell me if an entire column can be used as criteria in a
SUMPRODUCT formula? I tried: =-SUMPRODUCT(--('PS Sales'!$K:$K='Jan
09'!I$1),--('PS Sales'!$F:$F='Jan 09'!$A18),'PS Sales'!$H:$H) but got a #NUM!
error.

Using =-SUMPRODUCT(--('PS Sales'!$K$1:$K$60000='Jan 09'!I$1),--('PS
Sales'!$F$1:$F$60000='Jan 09'!$A18),'PS Sales'!$H$1:$H$60000) works fine, but
typing the row numbers every time is a huge waste of time!

Thanks!

--
Message posted via http://www.officekb.com