summing 3 columns in 1 sheet with SumIFS

Trying to sum 3 separate columns on 1 sheet with a SUMIFS with date criteria and shipping location criteria:

=SUMIFS([# Laptops]:[# Laptops] + [# Tablets]:[# Tablets] + [# Phones]:[# Phones], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston")

Getting #INVALID OPERATION

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    I don't think there's a way to trick SUMIFS to sum multiple ranges in a single function, I'd break it into the individual pieces

    =SUMIFS([# Laptops]:[# Laptops], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston") +

    SUMIFS([# Tablets]:[# Tablets], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston") +

    SUMIFS([# Phones]:[# Phones], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston")

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓

    I don't think there's a way to trick SUMIFS to sum multiple ranges in a single function, I'd break it into the individual pieces

    =SUMIFS([# Laptops]:[# Laptops], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston") +

    SUMIFS([# Tablets]:[# Tablets], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston") +

    SUMIFS([# Phones]:[# Phones], [Date Shipped]:[Date Shipped], >=DATE(2024, 1, 1), [Date Shipped]:[Date Shipped], <=DATE(2024, 1, 31), [Depot Ship]:[Depot Ship], "PHS - Pittston")

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Than worked thank you