| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting entries in 2+ columns
Toppers,
Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
You should be able to quite happily
=SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet name'!B1:B10="Construction")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
This works placed in Sheet2 wth data in Sheet1:
=SUMPRODUCT(--(Sheet1!A1:A10="UAE"),--(Sheet1!B1:B10="Construction")) (Perhaps you should state your requirements more clearly) "petess" wrote: Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
Toppers and Bob,
That's great. But how about selecting the entire column: =SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power")) i.e. B:B rather than B2:B457 ? "Bob Phillips" wrote: You should be able to quite happily =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet name'!B1:B10="Construction")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
No you can't select the entire column. SP works on arrays, and as such is
limited to a defined range. It can be upto 65535 rows, but not the entire column. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers and Bob, That's great. But how about selecting the entire column: =SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power")) i.e. B:B rather than B2:B457 ? "Bob Phillips" wrote: You should be able to quite happily =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet name'!B1:B10="Construction")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
SUMPRODUCT doesn't allow selection by columns. If you want to do that then
use B1:B65535. "petess" wrote: Toppers and Bob, That's great. But how about selecting the entire column: =SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power")) i.e. B:B rather than B2:B457 ? "Bob Phillips" wrote: You should be able to quite happily =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet name'!B1:B10="Construction")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting entries in 2+ columns
Thanks for your help, guys. Appreciated. Am very impressed!
"Bob Phillips" wrote: You should be able to quite happily =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet name'!B1:B10="Construction")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "petess" wrote in message ... Toppers, Further to my earlier post, however, I find that I cannot use the formula you suggest to work with data from ANOTHER worksheet within the same Excel file... I can still get the answers I need in the same worksheet, but this would be more elegant. Petess |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions | |||
| Table to pick out most common entries and count occurences of each | Excel Worksheet Functions | |||
| Hidden Columns in Shared Workbooks | Excel Discussion (Misc queries) | |||
| Counting differences in numbers across columns | Excel Worksheet Functions | |||
| Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Linear Mode
