CountIF but based on two columns

  • Thread starter Thread starter mika.
  • Start date Start date
M

mika.

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
 
mika. said:
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
Click to expand...

=SUMPRODUCT((A1:A100="P")*(B1:B100<>""))
 
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
 
They have to be of the same size, just replace the cell references with your
named ranges..

=sumproduct((abba="P" and so on
 
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.
 
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
 
mika

As Paul posted, you cannot use SUMPRODUCT() with entire columns. Restructure
your names to be A1:A65500, or similar.

Andy.
 
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.
 
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.

references with
your


.
Click to expand...
 
Thanks for the posts, it gave me a bit more direction where I was a bit
stumped earlier.

Here's a twist, let me restate the problem:

I have columns A and B. The need is to count the rows in A only if the
corresponding row in B is nonblank and if the item in A begins with a
substring "ZZZ_".

It looks like I may have to use some string manipulation but nothing
I've done has worked. :-(
 
One way

=SUMPRODUCT(--(LEFT(A2:A100,3)="ZZZ"),--(B2:B100<>""))

--

Regards,

Peo Sjoblom


Vanessa said:
Thanks for the posts, it gave me a bit more direction where I was a bit
stumped earlier.

Here's a twist, let me restate the problem:

I have columns A and B. The need is to count the rows in A only if the
corresponding row in B is nonblank and if the item in A begins with a
substring "ZZZ_".

It looks like I may have to use some string manipulation but nothing
I've done has worked. :-(


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
=SUMPRODUCT(--(LEFT($A$2:$A$10,4)="ZZZ_"),--($B$2:$B$10<>""))

Vanessa said:
Thanks for the posts, it gave me a bit more direction where I was a bit
stumped earlier.

Here's a twist, let me restate the problem:

I have columns A and B. The need is to count the rows in A only if the
corresponding row in B is nonblank and if the item in A begins with a
substring "ZZZ_".

It looks like I may have to use some string manipulation but nothing
I've done has worked. :-(


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top