sumifs not equal to; not working for QUERIED (table) data

[Runningeng]

I have some excel queries that return 11-digit numbers, but since they are part of a query, they come back as text. In my sumifs command, I want to avoid all of the data that has a certain 11-digit number. Thoughts?

SUMIFS(Inputs[Cost],Inputs[Row_Insert_Date],">="&Pivots!$D$3,Inputs[Row_Insert_Date],"<"&Pivots!$D$4,Inputs[11-digit],"<>"&99600000089)

Thanks!

 

[FormR]

Hi, welcome to the forum! I think you'll need to switch to an alternative to the SUMIFS() - here is one option.

=SUMPRODUCT(Inputs[Cost],--(Inputs[Row_Insert_Date]>=Pivots!$D$3),--(Inputs[Row_Insert_Date]<Pivots!$D$4),--(Inputs[11-digit]<>"99600000089"))

 

[Runningeng]

Hi, welcome to the forum! I think you'll need to switch to an alternative to the SUMIFS() - here is one option.

=SUMPRODUCT(Inputs[Cost],--(Inputs[Row_Insert_Date]>=Pivots!$D$3),--(Inputs[Row_Insert_Date]
"99600000089"))

That worked great! But why? I've used sumproduct, but not like this. What does the "--" in from of each component mean?

Thank you for your quick response!

 

[FormR]

What does the "--" in from of each component mean?

Hi, take a look at Aladin's explanation in post 6 of the link below:
https://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html#post987551