Sheet Summary Sumifs with dynamic date range and customer look up

LHoward_Jair

Hi,

I am having difficulty with my formulas referencing summary sheet cells.

My goal is to indicate a date range in the summary sheet cells, input a customer, and have it a summary cell show the sum of all of the quoted jobs for that customer with in the specified date range. My customers are in a multi check box drop down since more than one will bid on a job.

I was able to get the customer part to work:

=SUMIFS([Quoted Value]:[Quoted Value], [Customers Bidding]:[Customers Bidding], CONTAINS([Customer (Contains)]#, @cell), Status:Status, "quoted")

But when I tried to add the date range, I am getting #invalid operation. If I put an AND at the start of the bid date criteria, I get #invalid data type.

=SUMIFS([Quoted Value]:[Quoted Value], [Customers Bidding]:[Customers Bidding], CONTAINS([Customer (Contains)]#, @cell), Status:Status, "quoted", [Bid Date]:[Bid Date], >=[Start Date]#, @cell, <=[End Date]#)

What am I missing?

Thanks,

Nic Larsen

@Kelly Moore - I didn't test it, but I think your first suggestion just had an extra comma in there it didn't need after the 2nd @cell:

AND(@cell>=[Start Date]#, @cell, <=[End Date]#))

To this:

AND(@cell>=[Start Date]#, @cell <=[End Date]#))

Kelly Moore

Hey @LHoward_Jair

Try this

=SUMIFS([Quoted Value]:[Quoted Value], [Customers Bidding]:[Customers Bidding], CONTAINS([Customer (Contains)]#, @cell), Status:Status, "quoted", [Bid Date]:[Bid Date], AND(@cell>=[Start Date]#, @cell, <=[End Date]#))

Will it work for you?

Kelly

LHoward_Jair

That gave me an #invalid data type error. I am not sure what is invalid. The bid date column is set as a date type and to accept only dates. The start date and end date are also dates only.

Kelly Moore

@LHoward_Jair

To verify, your SUMIFS field is a text/number field?

Try adding a ISDATE(@cell), at the beginning of the AND

AND(ISDATE(@cell),@cell>=[Start Date]#, @cell, <=[End Date]#))

Does that make it work? The formula did work in my Test sheet

LHoward_Jair

Correct, the sumifs field is a text/number field.

This still gives me #invalid data type.

=SUMIFS([Quoted Value]:[Quoted Value], [Customers Bidding]:[Customers Bidding], CONTAINS([Customer (Contains)]#, @cell), Status:Status, "quoted", [Bid Date]:[Bid Date], AND(ISDATE(@cell), @cell, >=[Start Date]#, @cell, <=[End Date]#))

Nic Larsen

@Kelly Moore - I didn't test it, but I think your first suggestion just had an extra comma in there it didn't need after the 2nd @cell:

AND(@cell>=[Start Date]#, @cell, <=[End Date]#))

To this:

AND(@cell>=[Start Date]#, @cell <=[End Date]#))

LHoward_Jair

Yay! Removing the extra comma has worked.

Thank you both for helping me troubleshoot this :-)