Forum Discussion
Imported data pertaining to date not working with SUMIFS
I have been struggling to get SUMIFS to work by returning an amount based on two criteria, name of customer and date range for invoices within a calendar month.
Here is the formula:
=SUMIFS(ActRevMth[[#Headers],[Amount]],ActRevMth[[#Headers],[Name]],Revenue!B13,ActRevMth[[#Headers],[Date]],">="&Revenue!E3,ActRevMth[[#Headers],[Date]],"<="&Revenue!F3)
The cell in orange is where the formula sits and its criteria are in column B in yellow "name" and Date in column E which is 7/1/2025 shown as "Jul-25".
This is the data table I'm trying to test pulling the figure $131,849.00 from. The Date column was delimted and forced into MDY using text to columns.
My thanks to any advice anyone has...
4 Replies
Most probably
=SUMIFS( ActRevMth[Amount], ActRevMth[Name], Revenue!B13, ActRevMth[Date], ">="&Revenue!E3, ActRevMth[Date], "<="&Revenue!F3 )shall work
Can you please attach a sample file or share via OneDrive, Google-Drive, Dropbox or similar service so that we can check your data structure and type and suggest best formula for you?
Hello - thank you for reading and responding! Much appreciated.
I ran the test and it returned TRUE for both the name criteria as well as the date criteria. Yes the cell format is in number mode so its shows "-" for zero.
Because I needed to move on, I switched my approach and took the data table to create a pivot table, then made a static report from the pivot and used PRODUCTSUM(SUMIFS to get what I needed. It is disappointing though that I couldn't get the other, more elegant approach to work...
Best
Ryan=ActRevMth!D510=Revenue!B13
Does this formula return TRUE or FALSE? Perhaps there is a trailing space in one of these cells.
I assume that you've applied a custom format that shows a zero value in a cell as "-". Otherwise i wouldn't understand why the formula returns "-" instead of 0.
