Sumif question

Andre O

I'm trying to get a sum of one column based on choices from 2 other columns that have drop down choices. This is the formula that I entered based on the AI chatbot suggestion. It did not work. I am NOT a SS genius.

=SUMIF({Quote Status:Quote Status}, "In Progress", "Bid Submitted", "Results Pending", "On Hold" {Bid Success Probability:Bid Success Probability}, "Ag Partner 80%", {Quote Value:Quote Value})

Paul Newcome

Give this a try:

=SUMIFS([Quote Value]:[Quote Value], [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%")

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

Paul Newcome

Now you are just missing two commas. One after you close out each OR before you start the next range.

Paul Newcome

Can you explain the logic you need applied to the formula?

Andre O

Paul, I need to pick projects from Column "Quote Status" that have one of 4 of the possible statuses:

"In Progress", "Bid Submitted", "Results Pending", "On Hold". Of those, I need the projects that from the Bid Success Probability column have been tagged as "Ag Hot 80%". From those projects I need the sum of the quote values from the Quote Value Column.

I hope that helps.

Paul Newcome

Ok. And will this formula be on the same sheet as the data or a different sheet?

Andre O

Same sheet.

Paul Newcome

Give this a try:

=SUMIFS([Quote Value]:[Quote Value], [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%")

Andre O

That worked!! Thanks!

Andre O

If I wanted to do a count for those same items, could I just change the start of the formula to COUNTIFS?

=COUNTIFS([Quote Value]:[Quote Value], [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%")

Actually, I tried that and it dod not work…

Thoguhts?

Paul Newcome

Happy to help. 👍️

You would change it to a COUNTIFS and remove the first range.

Andre O

Hey, Paul! New day, new question.

For both of the formulas above what if I wanted to search for items in a certain date range? I have a column called Quote Complete, how would I set those formulas up to search for a specific month of quote complete dates, or a date range like the last 30 days?

Thank you so much for the help!

Paul Newcome

A specific month/year combo would look like this:

[Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = ##, IFERROR(YEAR(@cell), 0) = ####)

*(replace ## with the month number you are looking for and #### with the year number)

A date range would look more like this:

[Date Column]:[Date Column], AND(@cell >= TODAY(-30), @cell <= TODAY())

*(adjust the numbers inside of the TODAY function as needed)

Andre O

How would I add those to the SUMIF and COUNTIF formulas above to get those date ranges within the other formulas?

For the SUMIF I changed it to this, which does give me ALL of the awarded.

=SUMIFS([Quote Value]:[Quote Value], [Quote Status]:[Quote Status], OR(@ ), [Bid Success Probability]:[Bid Success Probability], @cell = "Ag Partner 80%")

Where would I add the date parts?

Thanks!

Andre O

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

I'm getting an UNPARSEABLE error with this.

Paul Newcome

You need to follow the same syntax as the rest of the range/criteria sets. The new range/criteria set should be inside of the SUMIFS and use commas to separate. The closing parenthesis after "AG Partner 80%" should be replaced with a comma and then put at the end of the formula.

Andre O

Hey, Paul. I assembled it thus, and still getting an error. I feel like I have comma's and parenteses wrong?

Sorry for being a pain…

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

Andre O

Actually it is this.

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