|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
December 4th, 2003, 02:11 PM
|
|||
|
|||
CountIF but based on two columns
Hello,
I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika 
|
|
#2
December 4th, 2003, 02:21 PM
|
|||
|
|||
|
CountIF but based on two columns
"mika." wrote in message
... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika =SUMPRODUCT((A1:A100="P")*(B1:B100""))
|
|
#3
December 4th, 2003, 02:21 PM
|
|||
|
|||
|
CountIF but based on two columns
Try something like: =SUMPRODUCT((A1:A100="P")*(B1:B100""))
-- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ---------------------------------------------------------------------------- "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika
|
|
#4
December 4th, 2003, 02:52 PM
|
|||
|
|||
|
CountIF but based on two columns
Thanks, but how might I do this if my columns were named
(i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ---------------------------------------------------------- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika .
|
|
#5
December 4th, 2003, 02:59 PM
|
|||
|
|||
|
CountIF but based on two columns
They have to be of the same size, just replace the cell references with your
named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ---------------------------------------------------------- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika .
|
|
#6
December 4th, 2003, 03:17 PM
|
|||
|
|||
|
CountIF but based on two columns
But note that you cannot use whole column references in SUMPRODUCT (such as
$A:$A or a name that refers to this). You can use a range up to $A1:$65535 or a name refering to this. "Peo Sjoblom" wrote in message ... They have to be of the same size, just replace the cell references with your named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ---------------------------------------------------------- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika .
|
|
#7
December 4th, 2003, 03:37 PM
|
|||
|
|||
|
CountIF but based on two columns
If the ranges must be the same size, then can I still use
names referring to entire columns? The formula =SUMPRODUCT((D_R"")*(A_P"")) where D_R and A_P are the names of entire columns, returns #NUM! Any ideas how I can get this to work where my names refer to entire columns? Thanks, Mika -----Original Message----- They have to be of the same size, just replace the cell references with your named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ------------------------------------------------------- --- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika . .
|
|
#8
December 4th, 2003, 03:50 PM
|
|||
|
|||
|
CountIF but based on two columns
mika
As Paul posted, you cannot use SUMPRODUCT() with entire columns. Restructure your names to be A1:A65500, or similar. Andy. "mika." wrote in message ... If the ranges must be the same size, then can I still use names referring to entire columns? The formula =SUMPRODUCT((D_R"")*(A_P"")) where D_R and A_P are the names of entire columns, returns #NUM! Any ideas how I can get this to work where my names refer to entire columns? Thanks, Mika -----Original Message----- They have to be of the same size, just replace the cell references with your named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ------------------------------------------------------- --- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika . .
|
|
#9
December 4th, 2003, 04:02 PM
|
|||
|
|||
|
CountIF but based on two columns
As Paul clarified, you can't use names referring to entire columns.
Redefine the names (via Insert Name Define) to ranges such as A1:A65535 or A2:A65536 (i.e. redefine the named ranges to cover just 1 row short of entire column) Then it'll work. -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ---------------------------------------------------------------------------- "mika." wrote in message ... If the ranges must be the same size, then can I still use names referring to entire columns? The formula =SUMPRODUCT((D_R"")*(A_P"")) where D_R and A_P are the names of entire columns, returns #NUM! Any ideas how I can get this to work where my names refer to entire columns? Thanks, Mika -----Original Message----- They have to be of the same size, just replace the cell references with your named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ------------------------------------------------------- --- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika . .
|
|
#10
December 4th, 2003, 04:22 PM
|
|||
|
|||
|
CountIF but based on two columns
Sorry, I didn't see this before. Thanks!
Mika -----Original Message----- But note that you cannot use whole column references in SUMPRODUCT (such as $A:$A or a name that refers to this). You can use a range up to $A1:$65535 or a name refering to this. "Peo Sjoblom" wrote in message ... They have to be of the same size, just replace the cell references with your named ranges.. =sumproduct((abba="P" and so on -- Regards, Peo Sjoblom "mika." wrote in message ... Thanks, but how might I do this if my columns were named (i.e. A is named abba and B is named beta)...is this possible then? Mika -----Original Message----- Try something like: =SUMPRODUCT((A1:A100="P")* (B1:B100"")) -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email ----------------------------------------------------- ----- ------------------ "mika." wrote in message ... Hello, I need to perform a calculation that I can't figure out... I have columns A and B. I want to count the number of rows where Column B is not blank, but I only want to count such rows if Column A says "P." How might I do this? Thanks for the help, Mika . .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
