Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: COUNTIFS for multiple criteria in multiple columns

  1. #1
    Join Date
    25th August 2013

    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")
    $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.

    I'm probably way freaking off with how I'm going about this, and look to your guidance for help

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    29th July 2010

    Re: COUNTIFS for multiple criteria in multiple columns

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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 10
    Last Post: February 27th, 2013, 04:35
  2. Countifs cells CONTAIN multiple criteria
    By Lennon in forum Excel General
    Replies: 5
    Last Post: March 13th, 2012, 05:36
  3. Replies: 3
    Last Post: March 3rd, 2012, 01:19
  4. Replies: 5
    Last Post: June 21st, 2011, 05:00
  5. Replies: 14
    Last Post: December 20th, 2006, 10:24


Posting Permissions

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