excel - SUMIFS with Cell Starts With and Two other Criteria - Stack Overflow

Hello fellow excel enthusiasts. Im trying to reference my database information and return the sum total of dollars from Col M. Criteria are Between date range, customer number, and PO# starts with KB...this is what I have so far but its not returning a total

=SUMIFS(Data!K:K,Data!G:G,">="&DATE(2025,3,1),Data!G:G,"<="&DATE(2025,3,31),Data!C:C,"301000",Data!E:E,LEFT("KB",2))

Hello fellow excel enthusiasts. Im trying to reference my database information and return the sum total of dollars from Col M. Criteria are Between date range, customer number, and PO# starts with KB...this is what I have so far but its not returning a total

=SUMIFS(Data!K:K,Data!G:G,">="&DATE(2025,3,1),Data!G:G,"<="&DATE(2025,3,31),Data!C:C,"301000",Data!E:E,LEFT("KB",2))

• excel
• excel-formula

Share Improve this question Follow edited 16 hours ago Mark S. 2,79911 gold badge99 silver badges2828 bronze badges asked 16 hours ago user30072115user30072115 2522 bronze badges 4

• 3 Looks like you have a table. Any reason to not use structured references in your formula? – BigBen Commented 16 hours ago
• 1 Also you describe column M yet your formula references K. Pay notice to BigBen's comment about referencing whole columns versus table references. You let Excel calculate each (used and unused) row per referenced column, which is very inefficiënt. – P.b Commented 14 hours ago
• 2 @P.bSUMIFS is optimized for full column references which is why I edited a similar statement out of the accepted answer. But I agree that structured references seem preferable. – BigBen Commented 12 hours ago
• 2 @BigBen Didn't know that. So using K:K versus K:.K would not make a difference using SUMIFS? – P.b Commented 12 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 4

=SUMIFS(Data!K:K,Data!G:G,">="&DATE(2025,3,1),Data!G:G,"<="&DATE(2025,3,31),Data!C:C,301000,Data!E:E,"KB*")

So there's two things I figure that may be happening. The 301000 may be reading it as text instead of values. Removing the quotes if this is the cause should help.

Additionally, the left 2 of KB is "KB", so instead use "KB*" as your criterion.