Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: COUNTIFS for multiple criteria in multiple columns

1. I agreed to these rules
Join Date
25th August 2013
Posts
1

## COUNTIFS for multiple criteria in multiple columns

Good Afternoon, Gurus,

In WORKSHEET 1, I have a large table:
Column A: Member Join Date
Column B: Member Name
Column C: Team Position
Column D: Team

Columns E - NE: Dates from 01/01/2013 to 31/12/13

There are 150+ rows listing those member details. The field between E2 and NE151 is intended to score the number of times one of those members submitted an inspection on a given date.

On WORKSHEET 2, I need a formula that tallies up the amount of inspections a team has conducted over the course of a month, noting that any inspections conducted by a person on date are not counted if their Member Join Date is after the reporting period. Columns A-D are so far sorted by Column B, but as this is intended to be a live document, it is not intended for these columns to be resorted into any other given order. I would prefer not to use VBA or PivotTables if at all possible.

So far, I've tried using the following formula, but I get a #VALUE! error.

=COUNTIFS('WORKSHEET 1'!\$A:\$A,">="&\$B\$2,'WORKSHEET 1!\$A:\$A,"<="&\$C\$2,'WORKSHEET 1'!\$D:\$D,\$A6,'WORKSHEET 1'!\$E:\$AI,">0")
Where:
\$B\$2 = Beginning date of month
\$C\$2 = End date of month
\$A\$6 = Team being referenced

The formula does work correctly as:

=COUNTIFS('WORKSHEET 1'!\$A:\$A,">="&\$B\$2,'WORKSHEET 1!\$A:\$A,"<="&\$C\$2,'WORKSHEET 1'!\$D:\$D,\$A6)

So I believe that the problems arise when I am trying to find out the total of inspections conducted.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: COUNTIFS for multiple criteria in multiple columns

Your last range is E:AI which COUNTIFS can not handle.
Try to play with SUMPRODCT.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno