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

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%")