• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif with column and row and area criteria...

Amanda92385

New Member
Hi - I've searched far and wide and can't find something online that fits (or I just didn't understand what I was reading...)

I have a spreadsheet set up with manager names horizontal across the top, let's say columns C-M, column A contains job codes (Job1, Job2, Job3, Job4) and column B has one of 3 names (regional, regional peer, national) then all the area in between, C2 - M3000 have a variety of head counts.

I don't want a sum. I want to count the number of jobs under John Smith that have headcounts for a regional job, regional peer job and national job

Here's what the spreadsheet would look like


JobCode
Cut John Smith Sally Sue Bob Brady Mark Jones
Job1
Regional 5 1 4 0
Job2
National 0 3 3 0
Job3
National 0 0 1 0
Job4
Regional Plus 1 0 0 10
Job5
Regional 3 0 0 1

So my results would look like this:

Manager Regional Regional Plus National
John Smith 2 1 0
Sally Sue 1 0 1
Bob Brady 1 0 2
Mark Jones 1 1 0


Any help would be so appreciated. I can't figure out what I'm supposed to do to get this to work. I read about lookups, mmult etc. but really just couldn't get it to work for me, I tried a slew of different things so any suggestions based on the example above would be so appreciated.

Thank you!!!!
Amanda
 
Hi Narayan,

I can't upload a copy of my workbook it's too large. Is it not possible to do it with what I provided or do you mean, you want me to put my example in an excel document and upload it?

Thank you!
Amanda
 
Hi,

We can't want your entire wb. Just share some rows of data with desired output as what you posted here doesn't clicking the solution.
Hope you got the point.
 
Ohh ok, got it. Attached is an example file. I put a few comments in.

Thank you for the help!!
 

Attachments

  • Example Workbook.xlsx
    10.3 KB · Views: 0
can I ask what Jobs!$1:$1 is? I'm not sure I understand that component... the formula doesn't work yet but I think I'm just misunderstanding what this part is supposed to be. I updated based on the data in my workbook. it worked fine in the test one but i keep getting an N/A.

Thank you thank you!
 
can I ask what Jobs!$1:$1 is? I'm not sure I understand that component... the formula doesn't work yet but I think I'm just misunderstanding what this part is supposed to be. I updated based on the data in my workbook. it worked fine in the test one but i keep getting an N/A.

Thank you thank you!

It is the first row on Jobs sheet where we match the manager names ref to col A on summary sheet.
 

Attachments

  • Example Workbook.xlsx
    11.2 KB · Views: 1
This is awesome! I think it will work fine. Excel keeps telling me there aren't enough resources to calculate but I'm hoping when I get into the office on Monday it will work. It seems to be saving so hopefully I won't have to recreate. Thank you so very much!!!
 
Back
Top