CountIF but based on two columns

[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

 

[Paul]

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<>""))

 

[Max]

Try something like: =SUMPRODUCT((A1:A100="P")*(B1:B100<>""))

 

[mika.]

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

 

[Peo Sjoblom]

They have to be of the same size, just replace the cell references with your
named ranges..

=sumproduct((abba="P" and so on

 

[Paul]

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.

 

[mika.]

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

 

[Andy B]

mika

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

Andy.

 

[Max]

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.

 

[mika]

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


.

 

[Vanessa]

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. :-(

 

[Peo Sjoblom]

One way

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

--

Regards,

Peo Sjoblom

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. :-(

creating financial statements

 

[Aladin Akyurek]

=SUMPRODUCT(--(LEFT($A$2:$A$10,4)="ZZZ_"),--($B$2:$B$10<>""))

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. :-(

creating financial statements