Solved

# COUNTIFS

Posted on 2011-05-09
2,456 Views
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

0
Question by:correlate
6 Comments

LVL 37

Expert Comment

Can you post a sample sheet?
0

Author Comment

Here we go - the full sheet, the formular is in the Sheet "Sums" & the row is highlighted in yellow
EXAMPLE.xlsx
0

LVL 85

Expert Comment

Is it OK to use helper columns on the Data sheet?
0

Author Comment

yep absolutely - thank you
0

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.
EXAMPLE--4-.xlsx
0

Author Closing Comment

Works a treat - thank you very very much
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

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.

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!