place counta within fomula?

  • Thread starter Thread starter Seeker
  • Start date Start date
S

Seeker

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:A65536)"))
Regards
 
Hi

Maybe this is what you need:

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

Regards,
Per
 
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?
 
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.
 
Back
Top