CountIf or another function
 

Hi,

Here is the data in my spreadsheet

ColumnA ColumnB

Open Jack
Open Robert
Closed Jack
In Progress Edward


I want to get a count of all Open, Closed and In Progress, but for each
individual. The output would be as follows and the formula must be contained
in a single cell:

Jack Robert Edward
Open # # #
Closed # # #
In Progress # # #

The CountIf works for me in terms of counting instances in a single column,
but I want to add an "AND" relationship to it and account for Column B.

Thank you,

SB

CountIf or another function
 

Being the table in Sheet1
and the result table in Sheet2
Enter this formula in B2 of Sheet2 and fill it to the right and down:
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=B$1))
Adjust ranges to real data!

Regards,
Stefi

€žSBO€� ezt Ã*rta:

Hi,

Here is the data in my spreadsheet

ColumnA ColumnB

Open Jack
Open Robert
Closed Jack
In Progress Edward


I want to get a count of all Open, Closed and In Progress, but for each
individual. The output would be as follows and the formula must be contained
in a single cell:

Jack Robert Edward
Open # # #
Closed # # #
In Progress # # #

The CountIf works for me in terms of counting instances in a single column,
but I want to add an "AND" relationship to it and account for Column B.

Thank you,

SB

CountIf or another function
 

Stefi,

That worked like a charm!!

Thanks so much.

SB

"Stefi" wrote:

Being the table in Sheet1
and the result table in Sheet2
Enter this formula in B2 of Sheet2 and fill it to the right and down:
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=B$1))
Adjust ranges to real data!

Regards,
Stefi

€žSBO€� ezt Ã*rta:

Hi,

Here is the data in my spreadsheet

ColumnA ColumnB

Open Jack
Open Robert
Closed Jack
In Progress Edward


I want to get a count of all Open, Closed and In Progress, but for each
individual. The output would be as follows and the formula must be contained
in a single cell:

Jack Robert Edward
Open # # #
Closed # # #
In Progress # # #

The CountIf works for me in terms of counting instances in a single column,
but I want to add an "AND" relationship to it and account for Column B.

Thank you,

SB

CountIf or another function
 

Hi,

You may also want to try pivot tables

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"SBO" wrote in message
...
Hi,

Here is the data in my spreadsheet

ColumnA ColumnB

Open Jack
Open Robert
Closed Jack
In Progress Edward


I want to get a count of all Open, Closed and In Progress, but for each
individual. The output would be as follows and the formula must be
contained
in a single cell:

Jack Robert
Edward
Open # #
#
Closed # #
#
In Progress # #
#

The CountIf works for me in terms of counting instances in a single
column,
but I want to add an "AND" relationship to it and account for Column B.

Thank you,

SB

CountIf or another function
 

You are welcome! Thanks for the feedback!
Stefi

€žSBO€� ezt Ã*rta:

Stefi,

That worked like a charm!!

Thanks so much.

SB

"Stefi" wrote:

Being the table in Sheet1
and the result table in Sheet2
Enter this formula in B2 of Sheet2 and fill it to the right and down:
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=B$1))
Adjust ranges to real data!

Regards,
Stefi

€žSBO€� ezt Ã*rta:

Hi,

Here is the data in my spreadsheet

ColumnA ColumnB

Open Jack
Open Robert
Closed Jack
In Progress Edward


I want to get a count of all Open, Closed and In Progress, but for each
individual. The output would be as follows and the formula must be contained
in a single cell:

Jack Robert Edward
Open # # #
Closed # # #
In Progress # # #

The CountIf works for me in terms of counting instances in a single column,
but I want to add an "AND" relationship to it and account for Column B.

Thank you,

SB