Can I Use a Count Function for Text?

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[djs]

Hi,
I need some big help pls. Im working on a sheet and Its giving me sho much
error. 1. I want to be able to have a automatic respoance come up in another
cell depending on the answer giving in one from a drop down list. E.g. If
"Service Department" is selected in C2 then "John Doe" would come up in E2.
What I want is no matter what Department I select it would give me the
correct manger instead of me having to type in the names or look in list to
try match name. List would be pre done

2. Im also trying to have a count done based on if Yes, No or N/A is input
from drop down list. Rows 1-200 has information but I need to have a count to
be done automatically and transfered to another sheet in same workbook with
the total amount of Yes, No or N/A.

3. Finally, say I select Service Deparment (which has 10 different job
position) from drop down list in C2, to have correct list of positions appear
in drop down list for D2 and depending on which position is selected in D2
correct list of Employees is in drop down list in E2.

thanks alot if anyone can help me. It would really ease my pain.

 

[Gord Dibben]

1. Use a VLOOKUP formula with a two column lookup table on another sheet.

2. =COUNTIF(Sheet1!A1:A200,"Yes") or "No" or "#N/A"

3. See Debra Dalgleish's site for dependent dropdowns.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[jolineachi]

I have another question:

I need to find the number of cases that were "Dismissed" in Column C between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160>="01/01/07")*(E149:E160<="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

 

[T. Valko]

The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160>="01/01/07")*(E149:E160<="12/31/07")

That doesn't match your description at all!

Try it like this:

=SUMPRODUCT(--(D149160="Dismissed"),--(E149:E160>=DATE(2008,1,1)),--(E149:E160<=DATE(2008,12,31)))

Better to use cells to hold the criteria:

A1 = Dismissed
B1 = start date
C1 = end date

=SUMPRODUCT(--(D149160=A1),--(E149:E160>=B1),--(E149:E160<=C1))

Or, if your time period is for the entire specific year:

=SUMPRODUCT(--(D149160=A1),--(YEAR(E149:E160)=2008))

 

[jolineachi]

I have another question:

I need to find the number of cases that were "Dismissed" in Column C between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149160="Guilty")*(E149:E160>="01/01/07")*(E149:E160<="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

 

[jolineachi]

Sorry! The formula is supposed to read "Dismissed". Does anyone know how to
delete a post?

 

[jolineachi]

Thanks T.Valko! The first formula is great for when I compile my semi-annual
reports and the third formula is perfect for my annual reports. Thanks a
bunch!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Nichole Beck]

I have a similar problem but your suggestion isn't working for me.

I have two columns of data that I want to count, but I only want to count
the intersection of the two columns. So, if A1 and B1 both contain the same
text I only want to count it once. Is that possible to do?

 

[T. Valko]

Maybe this:

=SUMPRODUCT(--(A1:A5=B1:B5))

...........A..........B
1........x...........x
2........y...........z
3........c...........c
4........s...........s
5........v..........w

Based on that sample data the result would be 3.

 

[gaelf]

I have been trying to calculate a column of Text in order to sum contents by
certain criteria

I have tried Count, CoutA and CountIF and cannot be able to get anything to
work.

Am I barking up the wrong tree? Can this be done?

Thanks much.

 

[gaelf]

I have been trying to calculate a column of Text in order to sum contents by
certain criteria

I have tried Count, CoutA and CountIF and cannot be able to get anything to
work.

Am I barking up the wrong tree? Can this be done?

Thanks much.

 

[Davina]

Here is my question:

How can I use a formula to create the following:

I want one column to be three-five different text (i.e. W, L, D, etc..) and
the next column to convert that text into a number (10, 5, 0, etc..)

I have been trying to figure this out all day, and any help is greatly
appreciated.

Thank you,

 

[Gord Dibben]

=LOOKUP(A1,{"D","L","W"},{0,5,10}) entered in B1

Note the lookup_vector {"D","L","W"} must be in ascending order.


Gord Dibben MS Excel MVP

 

[Richard Horn]

I found these comments very helpful so far. Now I am stumped.

I have a workbook with multiple worksheets and I am making a summary page up
front for management review.

Here's what I am trying to do. From worksheet named Q2, I have all my
projects leads in column D, and then the current status of their projects in
column E, for instance, intake, editing, pending, etc.. What I am trying to
do is count all the instances of one project lead, in this case chris craig,
and then I want to know all projects she is working on, unless, or except, if
the status is completed or carried forward. If the status for a project lead
is completed or carried, then do not count.

This is the formula I am trying to use, but is is counting all projects for
the given lead with all statuses, including completed and carried forward.

=SUMPRODUCT(('Q2'!D833="chris craig")*('Q2'!E8:E33<>"completed, carried
forward"))

 

[David Biddulph]

That probably means that you've got some slight difference in the content of
column E, perhaps spare spaces or other characters (such as non-breaking
spaces) in the cell.

You can check with a helper column:
=('Q2'!E8<>"completed, carried forward") and copy down
and/or
=('Q2'!E8="completed, carried forward") and copy down.

You might also want to try =LEN('Q2'!E8)=26

You might wish to copy the string (which you think is ="completed, carried
forward") from a suitable cell in column E into your formula.

 

[Richard Horn]

With the helper column I copied down, it did not return as it should:

Whether I used =('Q2'!E8<>"completed, carried forward") which returned all
TRUE even though column does contain a Completed status.

or

=('Q2'!E8="completed, carried forward") which returned all FALSE even though
column does contain a Completed status.

For column E on my Q2 worksheet I am using a Validation, List, from the
bottom of my sheet, so the project lead can have a drop-down box to select
their current status. So there would not be any abnormal characters or spaces
when I am trying to comprise my formula.

 

[Richard Horn]

For instance:

Project Lead Status Date
Chris Craig Client Testing 05/15/09
Chris Craig Completed 04/17/09

What I want to return is "1". In other words, all projects that "chris
craig" is working on, that the project status is not "completed" or the
status is not "carried forward".

hope this helps.

 

[Richard Horn]

Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<>"completed, carried forward") which returned all TRUE even
though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE even
though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does contain
a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them down to
cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me with, I
am not sure how to make this work, but I certainly appreciate your patience
is trying to share your experience.

thanks Richard