Sumif question

Paul Newcome

Now you are missing a "criteria" for the [Quote Status] range.

Andre O

Sorry, last correction. Cut and Paste is being weird. Still not working…

=SUMIFS([Quote Value]:[Quote Value], [Quote Status]:[Quote Status], OR(@cell = "Awarded", @cell = "Handed Off to PM"), AND(@cell >=TODAY (-30), @cell <= TODAY ()),[Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

Paul Newcome

Ok. You have your criteria for the [Quote Status] range, but you have dropped out your date range.

=SUMIFS([Quote Value]:[Quote Value], [Quote Status]:[Quote Status], OR(@cell = "Awarded", @cell = "Handed Off to PM"), NEED DATE RANGE HERE, AND(@cell >=TODAY (-30), @cell <= TODAY ()),[Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

Andre O

So, I think that this is right…it's still giving me an #UNPARSEABLE error. The date range is just the column right? Maybe I'm misunderstanding the date range fx.

=SUMIFS([Quote Value]:[Quote Value], [Quote Status]:[Quote Status], OR(@cell = "Awarded", @cell = "Handed Off to PM"), [Quote Completed]:[Quote Completed], AND(@cell >=TODAY (-30),@cell <= TODAY ()),[Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

Paul Newcome

I didn't realize until just now… There should not be a space between the end of TODAY and the parenthesis set that goes with it.

You have

TODAY (-30) and TODAY ()

but should have

TODAY(-30) and TODAY()

Andre O

OMG it worked!!! Thanks!

Paul Newcome

Happy to help. 👍️

Sorry I didn't notice those spaces sooner.

Andre O

New question!

So I have a group of items that are counting perfectly. The count is grabbing ALL of the items, how can I add to this formula to only grab items from 2024?

=COUNTIFS([Quote Status]:[Quote Status], OR(@cell = "In Progress", @cell = "Bid Submitted", @cell = "Results Pending", @cell = "On Hold"), [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

I added the TODAY function and just used (-265) to go back to the start of the year, and that worked, but I want it to be automated.

I did some research and tried this:

=COUNTIFS([Quote Status]:[Quote Status], OR(@cell = "In Progress", @cell = "Bid Submitted", @cell = "Results Pending", @cell = "On Hold"), [Quote Completed]:[Quote Completed], IFERROR(YEAR(@cell), 0) = 2024)), [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

The BOLD section is what I added.

I'm getting an UNPARSEABLE error…

Paul Newcome

@Andre O You need to remove one of the closing parenthesis from that section.

Andre O

=COUNTIFS([Quote Status]:[Quote Status], OR(@cell = "In Progress", @cell = "Bid Submitted", @cell = "Results Pending", @cell = "On Hold"), [Quote Completed]:[Quote Completed], IFERROR(YEAR(@cell),0) = 2024), [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

Still getting the UPARSABLE.

Paul Newcome

Sorry. It should have actually been both of them removed.

Andre O

That worked, and now of course I'm trying to add another condition to sort and I'm getting the UNPARSABLE again. I think I need to move where the Project Type fx is, or I'm having a bracket issue. Help!

=COUNTIFS([Quote Status]:[Quote Status], OR(@cell = "In Progress", @cell = "Bid Submitted", @cell = "Results Pending", @cell = "On Hold"), [Quote Completed]:[Quote Completed], IFERROR(YEAR(@cell), 0) = 2024, [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%",) [Project Type]:[Project Type], (@cell = "New Construction", @cell = "Remodel", @cell = "Addition", @cell = "Hybrid")

Paul Newcome

The range/criteria set should be inside of the parenthesis for the COUNTIFS. You also need the OR function before the criteria for the Project Type the same way you have the OR for the Quote Status.

Andre O

=COUNTIFS([Quote Status]:[Quote Status], OR(@cell = "In Progress", @cell = "Bid Submitted", @cell = "Results Pending", @cell = "On Hold", [Project Type]:[Project Type], OR(@cell = "New Construction", @cell = "Remodel", @cell = "Addition", @cell = "Hybrid")), [Quote Completed]:[Quote Completed], IFERROR(YEAR(@cell), 0) = 2024, [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

So I did this which seems correct…but I'm now getting an invalid data type error…

Paul Newcome

You forgot to close off the OR function before moving on to the [Project Type] range, and you have one too many closing parenthesis closing out the second OR.