Problem.... Nested countifs?

[Nick]

I want to be able to count the number of clients who are marked as "X" in
column Q .... and who are marked as "Low" in column M.

I tried countif... but it counted the number of clients marked Low and added
it to the number of X's.


tia
Nick.

[Don Guillett]

try
=sumproduct((q2:q200="X")*(m2:m200="Low"))

--
Don Guillett
SalesAid Software
donaldb@281.com
"Nick" <morpheseus@hotmail.com> wrote in message
news:BbB3e.878545$Xk.1243@pd7tw3no...
> I want to be able to count the number of clients who are marked as "X" in
> column Q .... and who are marked as "Low" in column M.
>
> I tried countif... but it counted the number of clients marked Low and
added
> it to the number of X's.
>
>
> tia
> Nick.
>
>
>

[Nick]

Thanks Don! This works perfectly. I modified the second half to give me a
range and that works too. I queried "Q2:Q500" for a value and then asked
for it to search the range "M2:P500". It added perfectly. This solves a
huge problem for me and save me a lot of time doing it manually.

I am very thankful for you suggestion!

Nick.


"Don Guillett" <donaldb@281.com> wrote in message
news:eFPYIE7NFHA.3356@TK2MSFTNGP12.phx.gbl...
> try
> =sumproduct((q2:q200="X")*(m2:m200="Low"))
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Nick" <morpheseus@hotmail.com> wrote in message
> news:BbB3e.878545$Xk.1243@pd7tw3no...
>> I want to be able to count the number of clients who are marked as "X" in
>> column Q .... and who are marked as "Low" in column M.
>>
>> I tried countif... but it counted the number of clients marked Low and
> added
>> it to the number of X's.
>>
>>
>> tia
>> Nick.
>>
>>
>>
>
>

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
donaldb@281.com
"Nick" <morpheseus@hotmail.com> wrote in message
news:hsY3e.892488$8l.266722@pd7tw1no...
> Thanks Don! This works perfectly. I modified the second half to give me
a
> range and that works too. I queried "Q2:Q500" for a value and then asked
> for it to search the range "M2:P500". It added perfectly. This solves a
> huge problem for me and save me a lot of time doing it manually.
>
> I am very thankful for you suggestion!
>
> Nick.
>
>
> "Don Guillett" <donaldb@281.com> wrote in message
> news:eFPYIE7NFHA.3356@TK2MSFTNGP12.phx.gbl...
> > try
> > =sumproduct((q2:q200="X")*(m2:m200="Low"))
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Nick" <morpheseus@hotmail.com> wrote in message
> > news:BbB3e.878545$Xk.1243@pd7tw3no...
> >> I want to be able to count the number of clients who are marked as "X"
in
> >> column Q .... and who are marked as "Low" in column M.
> >>
> >> I tried countif... but it counted the number of clients marked Low and
> > added
> >> it to the number of X's.
> >>
> >>
> >> tia
> >> Nick.
> >>
> >>
> >>
> >
> >
>
>

[CLR]

You can also use the Data > Filter > AutoFilter to filter these conditions
out and bring only them up on the screen for viewing...........

Vaya con dios,
Chuck, CABGx3



"Nick" <morpheseus@hotmail.com> wrote in message
news:BbB3e.878545$Xk.1243@pd7tw3no...
> I want to be able to count the number of clients who are marked as "X" in
> column Q .... and who are marked as "Low" in column M.
>
> I tried countif... but it counted the number of clients marked Low and
added
> it to the number of X's.
>
>
> tia
> Nick.
>
>
>

[Nick]

Yes... I had been doing that but our managers want to see a "Report" style
copy of our results.
Don saved me many hours work each week with his suggestion.

Nick.


"CLR" <croberts@tampabay.rr.com> wrote in message
news:%23tB98l7NFHA.1172@TK2MSFTNGP12.phx.gbl...
> You can also use the Data > Filter > AutoFilter to filter these conditions
> out and bring only them up on the screen for viewing...........
>
> Vaya con dios,
> Chuck, CABGx3
>
>
>
> "Nick" <morpheseus@hotmail.com> wrote in message
> news:BbB3e.878545$Xk.1243@pd7tw3no...
>> I want to be able to count the number of clients who are marked as "X" in
>> column Q .... and who are marked as "Low" in column M.
>>
>> I tried countif... but it counted the number of clients marked Low and
> added
>> it to the number of X's.
>>
>>
>> tia
>> Nick.
>>
>>
>>
>
>

[Ian]

Use a pivot table.
If the three columns are Client, M, and Q (you'll have
put name at the top for this -- lets call Q Yes/No and M
High/Low) highlight the entire range and do Data>>Pivot
table and just click Finish. This will put the pivot in
a new worksheet.

From the field list drag and drop "high/Low" into the Row
Fields, "Yes/No" into the column fields and "client" into
the data fields. Then right click on the data fields and
set "field settings" to count (if it's not already).
This will create a 2x2 table with the info you want. You
can click the down arrow buttons on each row/column to
hide and data you don't want to see.

[Nick]

I have about forty columns. The sheet manages client programming and
movement. We program them depending upon their assessment (L, M, or H). We
needed to report to managers how many of each catagory are taking what
programming. Don's suggestion did the trick perfectly.

Thanks,

Nick.


"Ian" <anonymous@discussions.microsoft.com> wrote in message
news:0c0001c537c8$49a7ae80$a501280a@phx.gbl...
> Use a pivot table.
> If the three columns are Client, M, and Q (you'll have
> put name at the top for this -- lets call Q Yes/No and M
> High/Low) highlight the entire range and do Data>>Pivot
> table and just click Finish. This will put the pivot in
> a new worksheet.
>
> From the field list drag and drop "high/Low" into the Row
> Fields, "Yes/No" into the column fields and "client" into
> the data fields. Then right click on the data fields and
> set "field settings" to count (if it's not already).
> This will create a 2x2 table with the info you want. You
> can click the down arrow buttons on each row/column to
> hide and data you don't want to see.
>