Formula with multiple ctriteria (Sum(Countif)

Happy Monday all,

I was wondering if it is possible to set two cirteria for an (=sum)? I currently use this formula for the department Totals

Please Login or Register  to view this content.

I then change {"..."} depending on the job status to get all of the totals.

The thing is they now want to see totals for each rep for each status.... Reps are in one colmun and the status in another. And since am asking they are also wanted to know the Average time per status that is in a thrid column.. lol

A pivot table would easily give you counts and averages, by rep, by status, as you need.

Originally Posted by kokapelly

=SUM(COUNTIF(RawData!A2:Q1715,{"Complete"}))

This syntax is only required if you are counting more than one item in the range, e.g.

=SUM(COUNTIF(RawData!A2:Q1715,{"Complete","Nearly Complete"}))

if you only have one status (Complete) you can use a simple COUNTIF like this

=COUNTIF(RawData!A2:Q1715,"Complete")

For 2 criteria in different columns, try COUNTIFS (with an "S") - it's more efficient than SUMPRODUCT

=COUNTIFS(RawData!F:F,"P101105",RawData!G:G,"Complete")

You can add more criteria, what is the criteria for the time column?

For averaging you can use AVERAGEIFS to average with conditions

Yeah, sure you can add an almost unlimited amount, e.g. for column R to be > 1

=COUNTIFS(RawData!F:F,"P101105",RawData!G:G,"Complete",RawData!R:R,">1")

If you want to average column R where the other two conditions are true use AVERAGEIFS like this:

=AVERAGEIFS(RawData!R:R,RawData!F:F,"P101105",RawData!G:G,"Complete")

The range to average (in green) always goes at the start, followed by range, condition, range, condition, range, condition.......etc.