Structured reference Formula help

A_Devasia 0 Reputation points
2023-06-23T00:02:45.24+00:00

Using Excel professional 2016. Formula should (successful on Microsoft 365) review the entire table and sum the amount where the category says Income for 2022. My experience is the formula operates on a row by row basis. If I have the table in "sheet 1" spanning the rows 1 - 10 and i input this formula in "sheet 2" row 2. The formula will evaluate row two of the table in "sheet 1" and if that row shows income and 2022 it will output the amount. I want it to operate similar to the sum if function and evaluate the whole table and provide me an aggregate sum of income for 2022. Formula: Sum( TableName [Amount] * (TableName (Category) = "Income") * (Year(TableName [Date]) = "2022"))

 

 

Microsoft 365 and Office | Excel | For business | Windows
{count} votes

1 answer

Sort by: Most helpful
  1. Lz._ 9,016 Reputation points
    2023-06-23T04:36:29.64+00:00
    Sum( TableName[Amount] * (TableName[Category] = "Income") * (Year(TableName[Date]) = "2022") )
    

    Above formula works on Microsoft 365 (or 2021) as-is thanks to dynamic arrays. On previous versions of Excel you have to validate it with Ctrl+Shift+Enter (on Windows) or use SUMPRODUCT in this case

    Though, I would follow Barry Schwarz suggestion and use SUMIFS for efficiency reasons at least

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.