asked on

Excel Help with a formula

I have a sheet that I need to do some counts on based on some text values.

I can count this way - =COUNTIF(B8:B12,"=n-****"). And I can count this way - =COUNTIF(E8:E12,"=p")+COUNTIF(E8:E12,"=t")+COUNTIF(E8:E12,"=e").

What I need to do is have something like this - IF(B8:B12,"=n-****")=COUNTIF(E8:E12,"=p")+COUNTIF(E8:E12,"=t")+COUNTIF(E8:E12,"=e")

Any help would be appreciated.

Ryan Chong

I don't quite understand your problem, what is "n", "p", "t", "e", etc ?

Do you have a sample excel can upload and indicate your issue accordingly?

Mark Wood

ASKER

the n-**** is the id number
p = present
t = tardy
e = excused

so if the usersID = n-**** then count the cells that have "p" "t" & "e" in them

Mark Wood

ASKER

Here is a copy of the file
Church-Attendee-List-Plus---EE.xlsx

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

You can do this by one formula by this..

=SUMPRODUCT((LEFT($B8:$B14,1)="N")*((D8:D14="p")+(D8:D14="t")+(D8:D14="e")))

This will give you the same results about what you are looking for...

Saurabh...

barry houdini

You could also use this formula to give a combined count

=SUM(COUNTIFS($B8:$B14,"n-*",D8:D14,{"p","t","e"}))

regards, barry

Note: using "****" doesn't do anything different from using a single "*" because the * wildcard denotes any number of characters (even zero) anyway - if you specifically want to count instances which have "n-" followed by four characters you would use ? to denote a single character, e.g. "n-????"

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.