Need Countif to work like Sumif
 

I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C" column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul

Need Countif to work like Sumif
 

=SUMPRODUCT(--(B7:B203="Paul"),--(ISTEXT(C7:C203)))

note that if you have formulas in C that can return a "" (blank looking
cell) they will be included, then you can use

=SUMPRODUCT(--(B7:B203="Paul"),--(ISTEXT(C7:C203)),--(LEN(C7:C203)0))

and of course if you only want to test for non empty cells in C

=SUMPRODUCT(--(B7:B203="Paul"),--(C7:C203<""))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"paulgallanter" wrote in message
...
I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C" column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul

Need Countif to work like Sumif
 

Either:

=SUMPRODUCT(--($B$7:$B$203="Paul G"),--ISTEXT($C$7:$C$203))

Or:

=SUM(IF($B$7:$B$203="Paul G",ISTEXT($C$7:$C$203)+0))

which needs to be confirmed with control+shift+enter, not just with enter.

paulgallanter wrote:
I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C" column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul

Need Countif to work like Sumif
 

hi!

something like

=SUMPRODUCT(--(B7:B203="PAUL"),--(C7:C203="*text you wanted*"))

-via135

paulgallanter Wrote:
I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C"
column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=531351

Need Countif to work like Sumif
 

Hi Paul,

Try these:

=SUMPRODUCT(--(B7:B203="Paul G"),--(C7:C203<""))
=SUMPRODUCT((B7:B203="Paul G")*(C7:C203<""))

or if apart from non-empty the value in column [C] must be text...

=SUMPRODUCT(--(B7:B203="Paul G"),--ISTEXT(C7:C203))
=SUMPRODUCT((B7:B203="Paul G")*ISTEXT(C7:C203))

Regards,
KL

"paulgallanter" wrote in message ...
I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C" column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul

Need Countif to work like Sumif
 

Try
=SUMPRODUCT(--(A1:A100="Paul G"),--(ISTEXT(B1:B100)))
Regards,
Alan.
"paulgallanter" wrote in message
...
I am trying to use the formula =Sumif(B7:B203,"Paul
G",(COUNTIF(C7:C203,"*"))) to count the number of cells in the "C" column
which contains text and are in the same row next to my name in the "B"
column. This formula does not work. Ay suggestions?

Thank you, paul