This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(16, 7.000000000000001%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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"))
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Try using SUMIFS(amount, category,"Income",date,2022)
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 65%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
@A_Devasia ,
Is there any progress in this thread? Please let us know if you still need assistance.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.