Sumifs w/ cross sheet references not working

LHoward_Jair

Hello,

I have a customer list and I am trying to use it to extract data from another sheet to get customer specific information.

My SUMIFS formula to total the $$ for all projects with a specific customer is working.

=SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row))

But when try to add additional criteria, I am getting #Incorrect Argument 

Limit results based on Status:

  =SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row),{BidBoard Status}, HAS(@cell, Status@row))

Limit result based on Date:

 =SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row),{BidBoard Bid Date}, AND(ISDATE@cell), @cell >= [Start Date]@row, @cell <= [End Date]@row)

The columns in the sheet I am working in are "Customer" (text/#), "Start Date" (date), "End Date"(date), and "Status"(single select drop down)

The columns in the sheet I am referencing "Quoted Value" (text/#), "Customers Bidding" (multi select drop down), Bid Date (date), and Status (single select drop down).

Am I missing a comma somewhere? Any advice is appreciated.

Paul Newcome

You missed the AND function for the date criteria.

=SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row), {BidBoard Bid Date}, AND(@cell >=[Start Date]@row, @cell <=[End Date]@row))

Paul Newcome

Double check that your cross sheet references are all referencing the same sheet and are all single columns by clicking on the appropriate column header when creating them.

LHoward_Jair

Thanks for the advice. I thought I had checked those but one had gotten disconnected.

Double checking the references fixed the limit results based on status equation, but the date equation is still not functional.

I am now getting the invalid operation error. I also am not able to put the AND(ISDATE@cell) back in without getting a syntax error.

=SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row), {BidBoard Bid Date}, @cell >=[Start Date]@row, @cell <=[End Date]@row)

LHoward_Jair

Well, I did not get the date range to work, but I did get a work around by creating helper columns to convert all the dates into year/month numbers and then search for matches by month using a sheet summary. Ideally, I would be able to get the range to work, but for now this is adequate.

Paul Newcome

You missed the AND function for the date criteria.

=SUMIFS({BidBoard Quoted Value}, {BidBoard Customers Bidding}, HAS(@cell, Customer@row), {BidBoard Bid Date}, AND(@cell >=[Start Date]@row, @cell <=[End Date]@row))

LHoward_Jair

That worked! Thanks Paul :-)

Paul Newcome

Happy to help. 👍️