Data in two cells - return a numeric value

[sphinxgyu18]

Hello all, I have spend the last couple of hours before posting this - I'm not a newbie and haven't figured out most of my formulas on this WS I am working on except for this.

I have a worksheet that has the following data

Column: K11, L11, M11 & N11 has people's names in it.
Column O11 - O200 has abbreviations to a service.

I need to create a forumla that says, If K11 (Juston) has CS (Calibration Services) inside Column O11, return a value.

So Juston has 4 CS, I need Excel to know that and display that is found 4 Juston's matching CS.

So if Mark has 50 PM's assigned to their name, I want Excel to display that it found through the whole sheet 50 PM's matching Mark's name.

Hope this makes sense.

Here is a snip of my Worksheet:

Capture.PNG

[TMS]

I think you need to be looking at COUNTIFS. That would seem to be appropriate for the first part and consistent formula use for the second part, although COUNTIF would probably do just as well as there's only one condition.


Regards, TMS

[sphinxgyu18]

TMS:

I have tried the "CountIFS" formula but no good.

Here is my formula, and it could be just me and how I am working the spreadsheet out in my head on how it should work.

Just to clarify, I am looking for Excel to look at a worksheet, if Juston has data in another column (CS) then total it up. Am I making sense?

Here is my formula:

=COUNTIF(Sheet3!G2:P5000,"=SR")

(Sheet 3 has all of the detail data on it, Sheet 1 is my summary sheet where I am trying to combine the data where it's a one stop show for the other info)

G2 cell has the employee's name. The "P" column has all of the CS, PM, IS, etc. etc. codes in it.

[sphinxgyu18]

Attached is copy of the data that I extracted.

Trying to figure out:
how many CS, ISFM, IS, Tim had.
how many CS, ISFM, IS Tim had.

...etc ...etc etc...


Worksheet-For-Web.xlsx

[jkray622]

I used the =COUNTIFS function. First I had to extract all Unique records for the Techs and the Services. I created a separate table.

There may be a more elegant way to do it, but I just set up each record to do four separate COUNTIF calculations, one for each of your Tech columns. An example of the COUNTIF formula is here:

Please Login or Register  to view this content.

I've attached the sheet with my table in it. I think there's at least one typo, because you have one Tech1 listed as CRE. But I added "him" as a tech just in case.

[janesmith123]

jkray622 (sorry, had to change screen names because excelforum lost my login over night):

So I have been working on this through the night and now into this morning and I still can not seem to get it to work - sort of. I am working between to worksheet in one file and I get data to pull over but when I update the 'Closed Tickets' tab, it does not update in the 'Summary' tab like it does in yours. Could you look at the attached spreadsheet and let me know what I am doing wrong?

For the record, I don't want you to complete the worksheet for me! This will be used by several people moving forward as a 'template and I am just trying to figure out the 'dirty work' so it makes others jobs easier. So I appreciate your help!


Februrary Summary - Updated-v2.xlsx

[ChemistB]

Your COUNTIFS in K9 of summary is counting as follows
Tim appears as Tech 1 for CS + as Tech 2 for ISFM + as Tech 3 for IS + Tech 4 as NEI
Is that what you wanted?

[ChemistB]

duplicate post

[janesmith123]

Yes, I am trying to get the spreadsheet to calcuate where ever Tim (either in Tech1, Tech2, Tech3, or Tech4 and has a CS, PM, etc in the Service column to add a number (total it up on the Summary sheet).

Does that make any sense?

[ChemistB]

Okay, that's not what you're doing. It's only counting Tim's CS's as Tech 1, only Tim's ISFM's as Tech 2, etc.

This formula in K9 gives 15 for Tim which I believe is the correct value

=SUMPRODUCT(('Closed Tickets'!$K$11:$N$1000=K$8)*(('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$2)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$3)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$4)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$5)))
Does that formula make sense to you?

[janesmith123]

ChemistB:

So I used your fomula:

=SUMPRODUCT(('Closed Tickets'!$K$11:$N$1000=I$8)*(('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$2)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$7)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$7)+('Closed Tickets'!$O$11:$O$1000='Closed Tickets'!$K$7)))

And changed it where Calvin should only show one Service Required (aka: Service Request) but the formula shows Calvin having three (3) Service Tickets when he really have only one. His name is only listed once in TECH1, TECH2, TECH3, TECH4 column with SR in the "Services" column. I do understand the formula and did make a few tweaks (as you see above) but I don't enough about text to number in Excel to write my own formula which is why I am asking for assistance.

Thanks again for your help - everyone's help!

[ChemistB]

Please Login or Register  to view this content.

In this last part is where you want to include all the catagories (from Closed TicketsK2:K8 that you want to count for Calvin. Here you are saying count CS's for Calvin (K2) once and PM's (K7) 3 times. Does that help?

[janesmith123]

Now I am getting even more confused. Am I looking for something that just maybe can't be done in Excel?

[ChemistB]

No, tell me what categories you want counted. I assume that maintenance would be the category codes in K2:K5?

[janesmith123]

K2:K7 (on the Closed Ticket Report)

CS Calibration Services
ISFM In Shop FM&ME
IS In Shop Repair
NEI New Equipment Install
PM Preventative Maintenance
SR Service Requested

So if Juston is in any of the Tech fields (TECH1, TECH2, TECH3, or TECH4) and in the "Service" column has SR, I want Juston to show on the Summary sheet that he did a Service Call. I am pulling the tech's name from a static field on the "Summary" page in I8 - Q8.

So if Juston (on the Closed Ticket tab) has his name once is each category (CS, ISFM, IS, NEI, PM, and SR) then on the summary sheet, I am looking for a 1 in each category under the header (name) "Juston" (as an example)

Capture2.PNG

[ChemistB]

Ahhh, okay, give me a minute

[janesmith123]

I thought I had it with one formula I came up with but A.) it wasn't updating dynamically and B.) it wasn't picking up the correct Service Name.

[ChemistB]

I inserted a column B for Code (see attachment) Then in J9 copied across and down

=SUMPRODUCT(('Closed Tickets'!$K$11:$N$1000=J$8)*('Closed Tickets'!$O$11:$O$1000=$B9))

[janesmith123]

Perfect! I feel like a dumb dumb for not figuring that out, it's simple but yet for some reason complex enough that I couldn't figure that out.

[janesmith123]

Extremely helpful and took the time to understand what I was trying to accomplish!

[ChemistB]

The more you use it, the more you'll figure it out. Glad I could help.

[janesmith123]

Just FYI

I reworked the formula, after I figured out the formula, understood the formula and now know how it works.

I changed it to:

=SUMPRODUCT(('Closed Tickets'!$J$12:$M$1001=H$9)*('Closed Tickets'!$D$12:$D$1001=$A10))

Basically, instead of using "Service Codes", I am capturing (using) what is already being put down since it won't change and using it against what is on the "Summary" page. So the rules look up what is on the "Closed Ticket" tab that is under D12:D1000 (which is the service that was performed) and then match it to the Summary sheet and look up the tech and then display the number.

Still, wouldn't of been able to do it w/o you. Thank you again, I most defiantly owe you one!