SUMIFS Syntax Issue

Question

Good afternoon.

I am attempting to create a SUMIFS formula to extract labor information from a table.

The formula needs to use two reference values:

Name (N2)

Date (P2)

The reference Table (Page titled "labor") has the following columns:

Formatted Name (Column A)

Hours (Column D)

Apply Date (Column H)

General Labor Category (Column K)

The following rules need to be created for the formula:

Sum of all values in Column C that follow the following rules:

Ensure name N2 matches the corresponding value from column A

Ensure Date P2 is greater than Column H value-30d

Ensure Date P2 is less than Column H value

Ensure labor category value matches filter (string value) (ex: "Time Worked")

Here is the formula as I have attempted to write it:

=SUMIFS(Labor!D:D,Labor!H:H,"<=P2",Labor!H:H,">=&P2-30",Labor!A:A,"="&N2,Labor!K:K,"=Time Worked")

The resulting value is zero for all rows of this expression. Any help would be much appreciated.

Answer 1

The reference to P2 must be outside the quotes.

=SUMIFS(Labor!D:D, Labor!H:H, "<="&P2, Labor!H:H, ">="&P2-30, Labor!A:A,N2, Labor!K:K, "Time Worked")

In your description, you mention P2 should be greater than H - 30 and less than H. If that is your intention, the formula should be

=SUMIFS(Labor!D:D, Labor!H:H, ">="&P2, Labor!H:H, "<="&P2+30, Labor!A:A,N2, Labor!K:K, "Time Worked")