Posted on 2011-05-09
Last Modified: 2012-05-11
Dear Experts,

I have large Excel 2010 spreadsheet where I can to do some analysis on rows.  I am trying to do a COUNTIFS formular, but I keep getting the wrong answer.  

Bascially the spreadsheet (Sheet name is Data) is a list of people, there is then then in columns D to R there are text values depicting somesone job responsibilities. Then in columns T to AH there are text values depicting the industry(s)

What I am trying to do is create a matrix of job responsibilities across industries.

So for example if I was looking for people with the following codes:

Job Responsibilites = "GeneralMgtNED" and an industry of "SERVICES"

the formular I am trying is =COUNTIFS(Data!D:R,"GeneralMgtNED",Data!T:AH,"SERVICES").  

I have also tried =COUNTIFS(Data!D:R,"=GeneralMgtNED",Data!T:AH,"=SERVICES").  

Both give me an answer of 5, it should be something like 28.

Can anybody help

Question by:correlate
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    Can you post a sample sheet?

    Author Comment

    Here we go - the full sheet, the formular is in the Sheet "Sums" & the row is highlighted in yellow
    LVL 85

    Expert Comment

    by:Rory Archibald
    Is it OK to use helper columns on the Data sheet?

    Author Comment

    yep absolutely - thank you
    LVL 85

    Accepted Solution

    Try this. The formula in AU9 on Data is set up to be copied left/right and up/down as required.

    Author Closing Comment

    Works a treat - thank you very very much

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    This article will show you how to use shortcut menus in the Access run-time environment.
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now