Sheet Summary Sumifs with dynamic date range and customer look up

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,

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

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]#))

All comments

Kelly Moore

Hey @LHoward_Jair

Try this

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.

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 :-)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.