COUNT based on conditions in 2 or more columns
 

SUMPRODUCT - or nested IF? I've been looking for help to 'join' together two
IF functions, but from what I've been reading it may be that I need to use
SUMPRODUCT. However, can't get either to work.

I want to count how many people who have a blank entry in one column, also
meet a given condition in another.

I have a spreadsheet with multiple columns which tells me about PC users who
have had their PCs ugraded. In my spreadsheet, a cell in column B gives me a
date that the user's PC was upgraded - the cell is blank if no upgrade has
taken place. Column U tells me whether they have a laptop or desktop and
there are no blank cells. So, in another summary sheet in the workbook, I
want to show some calculations - on this occasion the calculation will count
how many desktop users have not had their PCs upgraded.

Can you help?

COUNT based on conditions in 2 or more columns
 

=SUMPRODUCT(--(U2:U100="desktop"),--(B2:B100=""))


"Annie1904" wrote:

SUMPRODUCT - or nested IF? I've been looking for help to 'join' together two
IF functions, but from what I've been reading it may be that I need to use
SUMPRODUCT. However, can't get either to work.

I want to count how many people who have a blank entry in one column, also
meet a given condition in another.

I have a spreadsheet with multiple columns which tells me about PC users who
have had their PCs ugraded. In my spreadsheet, a cell in column B gives me a
date that the user's PC was upgraded - the cell is blank if no upgrade has
taken place. Column U tells me whether they have a laptop or desktop and
there are no blank cells. So, in another summary sheet in the workbook, I
want to show some calculations - on this occasion the calculation will count
how many desktop users have not had their PCs upgraded.

Can you help?

COUNT based on conditions in 2 or more columns
 

=SUMPRODUCT((D2:D22="")*(E2:E22="x"))
or a little safer in case someone touches the space bar in col D
=SUMPRODUCT((LEN(D3:D23)<2)*(E3:E23="x"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Annie1904" wrote in message
...
SUMPRODUCT - or nested IF? I've been looking for help to 'join' together
two
IF functions, but from what I've been reading it may be that I need to use
SUMPRODUCT. However, can't get either to work.

I want to count how many people who have a blank entry in one column, also
meet a given condition in another.

I have a spreadsheet with multiple columns which tells me about PC users
who
have had their PCs ugraded. In my spreadsheet, a cell in column B gives
me a
date that the user's PC was upgraded - the cell is blank if no upgrade has
taken place. Column U tells me whether they have a laptop or desktop and
there are no blank cells. So, in another summary sheet in the workbook, I
want to show some calculations - on this occasion the calculation will
count
how many desktop users have not had their PCs upgraded.

Can you help?

COUNT based on conditions in 2 or more columns
 

YES! - Got it! Super. Thanks. I love cracking new forumulae - I will be
using it all the time now! A.

"Teethless mama" wrote:

=SUMPRODUCT(--(U2:U100="desktop"),--(B2:B100=""))


"Annie1904" wrote:

SUMPRODUCT - or nested IF? I've been looking for help to 'join' together two
IF functions, but from what I've been reading it may be that I need to use
SUMPRODUCT. However, can't get either to work.

I want to count how many people who have a blank entry in one column, also
meet a given condition in another.

I have a spreadsheet with multiple columns which tells me about PC users who
have had their PCs ugraded. In my spreadsheet, a cell in column B gives me a
date that the user's PC was upgraded - the cell is blank if no upgrade has
taken place. Column U tells me whether they have a laptop or desktop and
there are no blank cells. So, in another summary sheet in the workbook, I
want to show some calculations - on this occasion the calculation will count
how many desktop users have not had their PCs upgraded.

Can you help?