No announcement yet.

COUNTIFS for multiple criteria in multiple columns

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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.‎