| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help in a fomula | Excel Discussion (Misc queries) | |||
| Fomula | Excel Discussion (Misc queries) | |||
| Please help, i need a fomula | Excel Worksheet Functions | |||
| IF Fomula | Excel Discussion (Misc queries) | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
