This formula had worked in the past, but I've noticed it's no longer calculating correctly. I have Multiple Sheets and am attemping to add up the total number of inventory items received across multiple sheets that match a certain SKU.

The below formula should sum Column C (numeric value, quantity of items received) across both the 'Inventory Entry' and 'Inventory Entry - DONE' sheets where the SKU (string, Column B of current sheet) matches Column H of the 'Inventory Entry' and 'Inventory Entry - DONE' sheets.

=ARRAYFORMULA(SUMIFS('Inventory Entry'!$C:$C,'Inventory Entry'!$H:$H,$B:$B)) ARRAYFORMULA(SUMIFS('Inventory Entry - DONE'!$C:$C,'Inventory Entry - DONE'!$H:$H,$B:$B))

I've seen other threads with different work-arounds using QUERY, but I'm unable to use those because Column C of my both my Inventory sheets can contain either a string or numberic value depending on the type of item being inventoried.

I've tried whittling the formula down to just 1 Inventory sheet but receive the same results.

As an example, a cell is returning '10' when there are 25 separate instances of the SKU in the Inventory sheet and it should be returning '305' total items. All the cells being summed contain numerical values and none of them have a value of '10'.

CodePudding user response：

ARRAYFORMULA and SUMIF don't interact as expected. Try with BYROW:

=BYROW(B:B,LAMBDA(each,SUMIFS('Inventory Entry'!$C:$C,'Inventory Entry'!$H:$H,each)) SUMIFS('Inventory Entry - DONE'!$C:$C,'Inventory Entry - DONE'!$H:$H,each))

CodePudding user response：

It appears this is an issue with using any function that uses any form of SUM. Opening a new thread.