CountIf Function Help Needed

[Mark]

I am trying setup a CountIf function where the following range and count
exists:

Column A Contains word "perm" or "term"
Column B Contains employee name
Column C Contains Number of hours worked - my be null value if employee
did not work.

I want a cell that counts the number of "term" or "perm" employees where the
value in Column C is not null or the value is >0. When ever I try to write
the count if it tells me that I am limited to one range as opposed to
multiple ranges: ie. A3:A22, A40:A75 because the employees are broken up
into teams. If need be I can dismantle the team setup but I would loose
pertinent information about each team. Then again, if the countIf function
works, then I can set up a countif cell that performs based on the multiple
ranges.

Hope this isn't too confusing.

Mark

 

[Govind]

Hi Mark,

Try

=SUMPRODUCT((C1:C100>0)*(A1:A100="Temp")) to count the number of temp
employees with hours more than zero.

Change "Temp" to "Perm" to do the same for Permanent employees. You dont
have to worry about the blank rows with no data (Rows 23 to 39 etc.).
Just make sure you cover the entire range where you have the data.

Regards

Govind.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A3:A22="perm"),--(C2:C22>0))

do one per team, then total all the subtotal formula in a regular sum, the
above formula
assumes that there is either a number or nothing in C



--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Mark]

This is great!!

I don't want to push it, but is it possible to right the same formula or with the following modification:

=SUMPRODUCT(--(A3:A22, A28:A41, A49:A66="perm"),--(C3:C22, C28:C41, C49:C66>0))

Which utilizes multiple ranges within the same function?

 

[Peo Sjoblom]

Can't you just sum them like

=SUM(SUMPRODUCT(--(A3:A22="perm"),--(C3:C22>0)),SUMPRODUCT(--(A28:A41="perm"),--(C28:C41>0)),SUMPRODUCT(--(A49:A66="perm"),--(C49:C66>0)))

--
Regards,

Peo Sjoblom

Portland, Oregon




This is great!!

I don't want to push it, but is it possible to right the same formula or
with the following modification:

=SUMPRODUCT(--(A3:A22, A28:A41, A49:A66="perm"),--(C3:C22, C28:C41,
C49:C66>0))

Which utilizes multiple ranges within the same function?