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.
 
Hi Per,
Well understood, thanks again for your enlightenment.
Regards
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can my formula handle inserted rows? 3
Loop required to merge worksheet data 2
SUMPRODUCT - use last cell function for range 2
auto numbering problem 2
Automatically Extend Formula in Count function with Additional Row 3
Sumproduct and CountA combined? Is that possible? 1
Summing the last 7 non-blank entries in a row of data 2
Change cell colour 2

Back
Top