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

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

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

Hi,

You may also want to try pivot tables

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"SBO" <(E-Mail Removed)> wrote in message
news:B4767B05-939B-4812-A972-(E-Mail Removed)...
> 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

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