0
votes

I am using this formula for SUMIF which is working fine, but I need to add another criteria which is the DATE. I only want the jobs to sum total if the date is the same in column A. How do I accomplish this? When I use SUMIFS I get an error "too few arguments".enter image description here

=SUMIF($B$2:$B$786,I2,$F$2:$F$786)
1
You want a =SUMIFS() then: =SUMIFS($F$2:$F$786, $B$2:$B$786, I2, $A$2:$A$786, today())JNevill
Remember that SUMIFS order is different. =SUMIFS(COLUMN_TO_SUM,CRIT_RANGE_1,CRIT_1,CRIT_RANGE_2,CRIT_2,...)Scott Craner
The parameter list is different in a SUMIFS from a SUMIF. SUMIF has the option to either use the summing column for criteria or another column for criteria. SUMIFS always has both. Look up the difference at support.office.com.user4039065

1 Answers

4
votes

Setting the Stage

When making my initial response, I didn't notice that the OP's sample data included two separate data ranges. There is the main "table" with columns including DATETIME, PRODUCT and AVG CYCLE MINUTES. These are the data to be summed across. However, the sums are not to appear against these rows, but against the rows in the second "table" which has the column REFERENCE LIST. REFERENCE LIST matches with the PRODUCT column in the first table and so provides one of the criteria for the SUMIF(S). The second criteria was to be DATE. However, since the second table doesn't include a date column it isn't obvious where this should come from.

In discussion with the OP, we realised that we wanted a the total (or, as it turned out, average) of "AVG CYCLE MINUTES" for each combination of DATETIME and PRODUCT. I walked the OP through the process of creating a PivotTable to do this.

Options

If we want to achieve the same or similar by means of SUMIF or SUMIFS, we need to make a decision about the DATE criteria. Either (a) the second table needs to include dates as well as PRODUCTs, or (b) we need to find a way to pick the date we're interested in.

Sum for Every Combination of DATE and PRODUCT

For option (a), an answer similar to my original one would work:

=SUMIFS(F:F,B:B,I2,A:A,J2)

where column J is the new column of dates. The full list of date/product combinations could have been swiftly produced by copying the relevant columns of the main data to elsewhere and using Excel's "remove duplicates" functionality.

Sum for single DATE per PRODUCT

For option (b), if you wanted to sum up all the data relating to "today", that could be done using:

=SUMIFS(F:F, B:B, I2, A:A, today())

(with credit to @JNevill).

What else might we want? Perhaps the most recent date the PRODUCT appears against? Or the earliest? If you have EXCEL 2016 (or better) ...

=SUMIFS(F:F, B:B, I2, A:A, MAXIFS(A:A,B:B,I2))

This would give the total minutes for the relevant PRODUCT on the latest date recorded for that PRODUCT. For the earliest date replace MAXIFS with MINIFS.

Alternatively, if the data is sorted so that the date you want will be the first to appear against any given PRODUCT you can use:

=SUMIFS(F:F, B:B, I2, A:A, INDEX(A:A,MATCH(I2,I:I,0)))

Miscellanea

I'd normally use VLOOKUP rather than INDEX/MATCH (a habit I've yet to acquire), but in the current data structure the columns are in the wrong order for that.

Since further discussion revealed that the OP actually wanted averages not totals, it's worth noting the existence of AVERAGEIFS.

NOTE: in my original answer (below), I reference the exact ranges rather than entire columns. There may be some marginal performance loss doing things as above, but I've never noticed. Though you need to be sure there are no extra data beneath the stuff you're interested in. On the other hand, there are definite benefits, it's easier to enter the formula and it will not need amending for larger (or smaller) data sets. (Thanks for reminding me of this @Jeeped)

NOTE 2: Since there is there is nothing you can do with SUMIF that can't also be done with SUMIFS, there is no harm in using the latter even when the former would suffice. That way you only have one function to remember and it's the most useful one. (Another good idea from @Jeeped)

Original Answer

As others have said, I think you want SUMIFS. Here the sum_range comes to the front, and you follow that with pairs of "criteria range"s and "criteria" like this:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In your case, I think that means you want:

=SUMIFS($F$2:$F$786,$B$2:$B$786,I2,$A$2:$A$786,A2)

When I wrote this, the relevant Microsoft Support page was here