How to perform a COUNTIF on multiple selections?


D

DavidJ726

I'm trying to perform a countif on two different ranges of cells but am
having difficulty figuring out how to get past the first range. This is
what I have that does work...

=COUNTIF(Sheet1!A13:AA39,"*David*")

I've tried nesting the ranges with parantheses a couple of different ways
but it didn't work. What would the proper expression be to add another
range of cells like below?

=COUNTIF(Sheet1!A13:AA39,"*David*")+(Sheet1!A57:AA53,"*David*")

Thanks,
David
 
Ad

Advertisements

T

T. Valko

Try this:

=COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*")

Biff
 
A

Alan

Try
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee"))
Note that with SUMPRODUCT you can't us A:A, you have to specify the range
like A1:A1000 or whatever, also both ranges must be the same, if you use
A1:A1000 then the other range must be B1:B1000.
Regards,
Alan.
KarenF said:
This is SO CLOSE to what I'm trying to do. The difference is I don't want
to
add the results of both columns, I want to count it once when both
conditions
are satisfied. The formula you suggest works great and is the closest
I've
come so far but it doubles my results.

Simplified example: I have a non-unique value in column B, which I want to
pair with the value in column A to make it unique.

A B
Calif employee [3 rows]
Colo employee [2 rows]

If I use the formula as you suggest,

=COUNTIF(A:A,"Calif")+COUNTIF(B:B,"employee")

I get a result of 6, when I only want to see 3.

It must be the "+" that's wrong but for the life of me I can't figure this
out.

Thanks,
KarenF
*******

T. Valko said:
Try this:

=COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*")

Biff
Click to expand...
Click to expand...
 
A

Alan

Yes it does I'm afraid, SUMPRODUCT doesn't support wildcards. Do you have to
use the wildcard? Is the word 'employee' always in the same place in each
cell? If it's always at the beginning of the cell for instance,
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500=LEFT("employee",8)))
Alan.
KarenF said:
That works until I look for wild cards in column b.

=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee*"))

Does SUMPRODUCT have something against wild cards in text?

KarenF

Alan said:
Try
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee"))
Note that with SUMPRODUCT you can't us A:A, you have to specify the range
like A1:A1000 or whatever, also both ranges must be the same, if you use
A1:A1000 then the other range must be B1:B1000.
Regards,
Alan.
KarenF said:
This is SO CLOSE to what I'm trying to do. The difference is I don't
want
to
add the results of both columns, I want to count it once when both
conditions
are satisfied. The formula you suggest works great and is the closest
I've
come so far but it doubles my results.

Simplified example: I have a non-unique value in column B, which I want
to
pair with the value in column A to make it unique.

A B
Calif employee [3 rows]
Colo employee [2 rows]

If I use the formula as you suggest,

=COUNTIF(A:A,"Calif")+COUNTIF(B:B,"employee")

I get a result of 6, when I only want to see 3.

It must be the "+" that's wrong but for the life of me I can't figure
this
out.

Thanks,
KarenF
*******

:

Try this:

=COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*")

Biff

I'm trying to perform a countif on two different ranges of cells but
am
having difficulty figuring out how to get past the first range.
This
is
what I have that does work...

=COUNTIF(Sheet1!A13:AA39,"*David*")

I've tried nesting the ranges with parantheses a couple of different
ways
but it didn't work. What would the proper expression be to add
another
range of cells like below?

=COUNTIF(Sheet1!A13:AA39,"*David*")+(Sheet1!A57:AA53,"*David*")

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
A

Alan

Better still,
=SUMPRODUCT(--(A1:A500="Calif"),--(ISNUMBER(SEARCH("employee",B1:B500))))
Regards,
Alan.
Alan said:
Yes it does I'm afraid, SUMPRODUCT doesn't support wildcards. Do you have
to use the wildcard? Is the word 'employee' always in the same place in
each cell? If it's always at the beginning of the cell for instance,
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500=LEFT("employee",8)))
Alan.
KarenF said:
That works until I look for wild cards in column b.

=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee*"))

Does SUMPRODUCT have something against wild cards in text?

KarenF

Alan said:
Try
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee"))
Note that with SUMPRODUCT you can't us A:A, you have to specify the
range
like A1:A1000 or whatever, also both ranges must be the same, if you use
A1:A1000 then the other range must be B1:B1000.
Regards,
Alan.
This is SO CLOSE to what I'm trying to do. The difference is I don't
want
to
add the results of both columns, I want to count it once when both
conditions
are satisfied. The formula you suggest works great and is the closest
I've
come so far but it doubles my results.

Simplified example: I have a non-unique value in column B, which I
want to
pair with the value in column A to make it unique.

A B
Calif employee [3 rows]
Colo employee [2 rows]

If I use the formula as you suggest,

=COUNTIF(A:A,"Calif")+COUNTIF(B:B,"employee")

I get a result of 6, when I only want to see 3.

It must be the "+" that's wrong but for the life of me I can't figure
this
out.

Thanks,
KarenF
*******

:

Try this:

=COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*")

Biff

I'm trying to perform a countif on two different ranges of cells
but am
having difficulty figuring out how to get past the first range.
This
is
what I have that does work...

=COUNTIF(Sheet1!A13:AA39,"*David*")

I've tried nesting the ranges with parantheses a couple of
different
ways
but it didn't work. What would the proper expression be to add
another
range of cells like below?

=COUNTIF(Sheet1!A13:AA39,"*David*")+(Sheet1!A57:AA53,"*David*")

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 
Ad

Advertisements

A

Alan

You're welcome,
Alan.
KarenF said:
Sweet! I don't understand why, I'll research ISNUMBER which is new to me
(as
is SUMPRODUCT), but this one works like a charm. I never, ever would have
figured this out myself, thanks much.

Alan said:
Better still,
=SUMPRODUCT(--(A1:A500="Calif"),--(ISNUMBER(SEARCH("employee",B1:B500))))
Regards,
Alan.
Alan said:
Yes it does I'm afraid, SUMPRODUCT doesn't support wildcards. Do you
have
to use the wildcard? Is the word 'employee' always in the same place in
each cell? If it's always at the beginning of the cell for instance,
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500=LEFT("employee",8)))
Alan.

That works until I look for wild cards in column b.

=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee*"))

Does SUMPRODUCT have something against wild cards in text?

KarenF

:

Try
=SUMPRODUCT(--(A1:A500="Calif"),--(B1:B500="employee"))
Note that with SUMPRODUCT you can't us A:A, you have to specify the
range
like A1:A1000 or whatever, also both ranges must be the same, if you
use
A1:A1000 then the other range must be B1:B1000.
Regards,
Alan.
This is SO CLOSE to what I'm trying to do. The difference is I
don't
want
to
add the results of both columns, I want to count it once when both
conditions
are satisfied. The formula you suggest works great and is the
closest
I've
come so far but it doubles my results.

Simplified example: I have a non-unique value in column B, which I
want to
pair with the value in column A to make it unique.

A B
Calif employee [3 rows]
Colo employee [2 rows]

If I use the formula as you suggest,

=COUNTIF(A:A,"Calif")+COUNTIF(B:B,"employee")

I get a result of 6, when I only want to see 3.

It must be the "+" that's wrong but for the life of me I can't
figure
this
out.

Thanks,
KarenF
*******

:

Try this:

=COUNTIF(Sheet1!A13:AA39,"*David*")+COUNTIF(Sheet1!A57:AA53,"*David*")

Biff

I'm trying to perform a countif on two different ranges of cells
but am
having difficulty figuring out how to get past the first range.
This
is
what I have that does work...

=COUNTIF(Sheet1!A13:AA39,"*David*")

I've tried nesting the ranges with parantheses a couple of
different
ways
but it didn't work. What would the proper expression be to add
another
range of cells like below?

=COUNTIF(Sheet1!A13:AA39,"*David*")+(Sheet1!A57:AA53,"*David*")

Thanks,
David
Click to expand...
Click to expand...
Click to expand...
 

Top