I have a workbook that is importing data from another workbook. It is pulling all the data in just fine using
=query(importrange("1iY25u07bWHgEYywGmO3S9QTTQsuHOANQBysL9zg7CGI","DATA!A2:AD5000"), "select * where Col30 <> '' and Col30 = 'District Manny'")
The issue is that in Column AB I have Payments displaying and they show up fine in the sheet. But when I make another sheet to show totals it just shows 0.
=SUM(filter(DATA!AB:AB,DATA!A:A=A16))
If I take out the SUM from above it shows all the values that match the criteria. I want to sum those values not show all values. The bizarre part is that in column AA I have similar values that work perfectly with the above formula.
The only difference is in the original sheet the AB column looks like this
=if(isblank(Z8)," ",Z8-AA8)
Am I missing something big here?
it's a formatting issue caused by " " in =IF(ISBLANK(Z8), " ", Z8-AA8)
you can fix it directly there like:
=IF(ISBLANK(Z8), , Z8-AA8)
or use SUMPRODUCT instead of SUM like:
=SUMPRODUCT(FILTER(DATA!AB:AB, DATA!A:A=A16))