Hi

With the help Pete UK of an earlier thread used the following formula

sumproduct(--(sheetname!A1:a20="GA01")--(sheetname!B1:b20=105))

What I now want to do is use the indirect function with sumproduct in a
summary sheet

sumproduct(--(""&c1&"!A1:a20,a6)--indirect(""&c1"!b1:b20=105

Cell A6 contains the first criteria (GA01)
Cell C1 contains the shhet name


Help would be appriciated as it is not working !

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")="GA01")*( INDIRECT("'"&C1&"'!
B1:B20")=105))

HTH
Kostis Vezerides

On Nov 22, 4:38 pm, "BNT1 via OfficeKB.com" <u19326@uwe wrote:
Hi

With the help Pete UK of an earlier thread used the following formula

sumproduct(--(sheetname!A1:a20="GA01")--(sheetname!B1:b20=105))

What I now want to do is use the indirect function with sumproduct in a
summary sheet

sumproduct(--(""&c1&"!A1:a20,a6)--indirect(""&c1"!b1:b20=105

Cell A6 contains the first criteria (GA01)
Cell C1 contains the shhet name

Help would be appriciated as it is not working !

Brian

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

thanks Kostis

yes, it works ok,
However, I want the first criteria "GA01" to refer to cell A6, this way i
will drag down into other cells

any idea's

regards

vezerid wrote:
=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")="GA01")* (INDIRECT("'"&C1&"'!
B1:B20")=105))

HTH
Kostis Vezerides

Hi

[quoted text clipped - 16 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

Simply replace ="GA01" with A6. It is not part of the INDIRECT.

=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")=A6)*(INDI RECT("'"&C1&"'!
B1:B20")=A7))

Regards
Kostis

On Nov 22, 9:10 pm, "BNT1 via OfficeKB.com" <u19326@uwe wrote:
thanks Kostis

yes, it works ok,
However, I want the first criteria "GA01" to refer to cell A6, this way i
will drag down into other cells

any idea's

regards

vezerid wrote:
=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")="GA01")* (INDIRECT("'"&C1&"'!
B1:B20")=105))

HTH
Kostis Vezerides

Hi

[quoted text clipped - 16 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

thanks Kostis for the speedy response,

worked a treat

regards

vezerid wrote:
Simply replace ="GA01" with A6. It is not part of the INDIRECT.

=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")=A6)*(IND IRECT("'"&C1&"'!
B1:B20")=A7))

Regards
Kostis

thanks Kostis

[quoted text clipped - 20 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200711/1

Thanks for the feedback.

Regards

On Nov 22, 9:50 pm, "BNT1 via OfficeKB.com" <u19326@uwe wrote:
thanks Kostis for the speedy response,

worked a treat

regards

vezeridwrote:
Simply replace ="GA01" with A6. It is not part of the INDIRECT.

=SUMPRODUCT((INDIRECT("'"&C1&"'!A1:A20")=A6)*(IND IRECT("'"&C1&"'!
B1:B20")=A7))

Regards
Kostis

thanks Kostis

[quoted text clipped - 20 lines]
--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200711/1