Forum Discussion
SUMIF and XLOOKUP not working
I have two workbooks (attached).
I am trying to retrieve values from one workbook, depending upon an expense category, into the second workbook. (This first workbook [Sample Tolls] has incorrect values resulting from a malfunctioning SUMIF that has been reported her in a previous post) that uses the formula
=SUMIF($B4,"Taxi",$C4:$E4).
In the second workbook [Sample Taxi Expenses] I am trying to use XLOOKUp to match a date and category to retrieve the correct date from the first workbook [Sample Tolls].
The formula I am using is
=XLOOKUP($C$4:$C$34,'[Sample Tolls.xlsx]Tolls'!$A$4:$A$34,'[Sample Tolls.xlsx]Tolls'!$F$4:$F$34,0,1)
This not only creates a SPILL error but also does not return a correct value.
Any idea of what I am doing wrong in BOTH worksheets?
SUMIF() operates on rows and not on columns.
You don't need to copy the XLOOKUP() down. Because you use multiple criteria cells the formula spills. But the formula is looking for a category in a date column. Not much matching there.
So, I propose a different data structure. Rows without data are omitted.
