place counta within fomula?
 

Thus end row can auto change to the last row?
like
=SUMPRODUCT(--(A1:A"counta(A1:A65536)"="xxx"),--(E1:E"counta(A1:A65536)"="bbb"),(F1:F"counta(A1:A6 5536)"))
Regards

place counta within fomula?
 

Hi

Maybe this is what you need:

=SUMPRODUCT(--(A1:A65535="xxx");--(E1:E65535="bbb");F1:F65535)

Regards,
Per

"Seeker" skrev i meddelelsen
...
Thus end row can auto change to the last row?
like
=SUMPRODUCT(--(A1:A"counta(A1:A65536)"="xxx"),--(E1:E"counta(A1:A65536)"="bbb"),(F1:F"counta(A1:A6 5536)"))
Regards

place counta within fomula?
 

Hi Per,
Thanks for your prompt reply and your answer works.
Just want to know further reason of while range is A1:A65535 or A2:A65536 (1
line short of full rows), it is no problem in extracting the sum but shows
#Num if I change the range to A1:A65536?

"Per Jessen" wrote:

Hi

Maybe this is what you need:

=SUMPRODUCT(--(A1:A65535="xxx");--(E1:E65535="bbb");F1:F65535)

Regards,
Per

"Seeker" skrev i meddelelsen
...
Thus end row can auto change to the last row?
like
=SUMPRODUCT(--(A1:A"counta(A1:A65536)"="xxx"),--(E1:E"counta(A1:A65536)"="bbb"),(F1:F"counta(A1:A6 5536)"))
Regards

place counta within fomula?
 

Hi Seeker

Thanks for your reply.

A1:A65536 is converted to A:A (the entire column) and excel functions can
not handle an entire column (or row) as reference in versions prior to excel
2007.

"Seeker" skrev i meddelelsen
...
Hi Per,
Thanks for your prompt reply and your answer works.
Just want to know further reason of while range is A1:A65535 or A2:A65536
(1
line short of full rows), it is no problem in extracting the sum but shows
#Num if I change the range to A1:A65536?

"Per Jessen" wrote:

Hi

Maybe this is what you need:

=SUMPRODUCT(--(A1:A65535="xxx");--(E1:E65535="bbb");F1:F65535)

Regards,
Per

"Seeker" skrev i meddelelsen
...
Thus end row can auto change to the last row?
like
=SUMPRODUCT(--(A1:A"counta(A1:A65536)"="xxx"),--(E1:E"counta(A1:A65536)"="bbb"),(F1:F"counta(A1:A6 5536)"))
Regards

place counta within fomula?
 

Hi Per,
Well understood, thanks again for your enlightenment.
Regards

"Per Jessen" wrote:

Hi Seeker

Thanks for your reply.

A1:A65536 is converted to A:A (the entire column) and excel functions can
not handle an entire column (or row) as reference in versions prior to excel
2007.

"Seeker" skrev i meddelelsen
...
Hi Per,
Thanks for your prompt reply and your answer works.
Just want to know further reason of while range is A1:A65535 or A2:A65536
(1
line short of full rows), it is no problem in extracting the sum but shows
#Num if I change the range to A1:A65536?

"Per Jessen" wrote:

Hi

Maybe this is what you need:

=SUMPRODUCT(--(A1:A65535="xxx");--(E1:E65535="bbb");F1:F65535)

Regards,
Per

"Seeker" skrev i meddelelsen
...
Thus end row can auto change to the last row?
like
=SUMPRODUCT(--(A1:A"counta(A1:A65536)"="xxx"),--(E1:E"counta(A1:A65536)"="bbb"),(F1:F"counta(A1:A6 5536)"))
Regards