No one gets it... is there a number I can call?

[Guest]

Below is a forum I've posted many times on many websites. I don't believe it
can't be done. I need a count of each person in one cell. but im being told
each name must be on its own cell to be counted properly "add a row" "add a
cell" I'm hearing... WAY TOO REDUNDANT!! So is there a help line I can call??


EXTREMELY IMPORTANT!

My Pivot Table

example.

"SPREADSHEET"
DATE JOB CONTRACTOR
1/1 1 A

1/2 2 B \
A > One Cell
C /

1/3 3 C

when I graph how many time each contractor is used its...

"MY UNWANTED GRAPH"
contractor A.... 1 time
contractor B [] A [] C... 1 time
contractor C... 1 time


How, if at all possible can I make it...

"WHAT I WANT"
contractor A... 2 times
contractor B... 1 time
contractor C... 2 times


"contractor B [] A [] C... 1 time"

notes:
=SUM(IF(ISNUMBER(H3:H43),1,0)) Thats my formula for my pivot table

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(SEARCH("A",A2:A20))))

etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob's solution works in the simplistic case where you have single letter
codes for the contractors. It may even work if you have uniquely named
contractors. It may not if there is much overlap in the spelling of the
names.

However, the advice you got from others isn't WAY TOO REDUNDANT, it's the
appropriate guidance trying to help you understand that the approach you
started with makes EVERYTHING else really, really HARD. Putting multiple
entries in single cells is contrary to what most power users would tell you
is advisable or best practice. Instead of facilitiating analysis, your
structure inhibits it.

 

[Sandy Mann]

Do you mean that the B A C are vertucally aligned in the cell with line
breaks between them?

If so try:

=COUNTIF(C2:C6,"A"&CHAR(10)&"B"&CHAR(10)&"C")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk