Adding values when certain criteria is met

Bigsby

Hello, I am trying to figure out how to add values from one cell, when certain criteria is met in 2 other cells. Example: Add values from "Forecast Value" cell, when "Sales Status" cell is Closed Won and "Date Received" cell is for a particular month. April=04/01/22

Michael Culley

That means there's an error so the iferror statement makes errors show as blanks.

What if you tried this formula instead?

=SUMIFS({Archive-Mathew Truster Leads Range 1}, {Archive-Mathew Truster Leads Range 2}, "Closed Won", {Archive-Mathew Truster Leads Range 3},IFERROR(MONTH(@cell), 0) = 1)

Also you should get into the practice of naming your ranges and not using the default names. Leaving the default range names can cause random circular reference errors out of the blue for no reason at all.

Andrée Starå

Hi @Bigsby

I hope you're well and safe!

Try something like this.

=IFERROR(SUMIFS([Forecast Value]:[Forecast Value], [Sales Status]:[Sales Status], "Closed Won", [Date Received]:[Date Received], MONTH(@cell) = 4), "")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bigsby

Hi @Andrée Starå

That was perfect!! Thank you so much! Have a wonderful rest of your week as well!!

Andrée Starå

@Bigsby

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Michael Culley

Using Andree's formula could give you the error of "INVALID DATA TYPE" which would result in a blank cell if for any reason the date is blank on any of the rows. To prevent that you could change the formula to this one:

=SUMIFS([Forecast Value]:[Forecast Value], [sales status]:[sales status], "Closed Won", [Column6]:[Column6], 2, [date received]:[date received], IFERROR(MONTH(@cell), 0) = 4)

Andrée Starå

@Michael Culley

Thanks! Good catch! I missed that!

Bigsby

Thanks Michael. The formula seemed to work as it should, however for two of the people, the formula is not generating a value like it does for the others. Here is a screenshot of the sheet with the formula I am using.

pattatha9

Thanks! Good catch!

Bigsby

@Michael Culley @Andrée Starå The formula seemed to work as it should, however for two of the people, the formula is not generating a value like it does for the others. Here is a screenshot of the sheet with the formula I am using. Any recommendations? There are not any blank cells for the date column.

Michael Culley

That means there's an error so the iferror statement makes errors show as blanks.

What if you tried this formula instead?

=SUMIFS({Archive-Mathew Truster Leads Range 1}, {Archive-Mathew Truster Leads Range 2}, "Closed Won", {Archive-Mathew Truster Leads Range 3},IFERROR(MONTH(@cell), 0) = 1)

Also you should get into the practice of naming your ranges and not using the default names. Leaving the default range names can cause random circular reference errors out of the blue for no reason at all.

Bigsby

That formula worked perfect! Thank you! Also, thanks for the heads up with naming the ranges!!

Bigsby

@Michael Culley @Andrée Starå

Good Day Gentlemen,

Sorry to bring this up again, but I'm still having trouble getting this formula to work. There are no blank cells in the sheet I am referencing and Im assuming that since the value is coming back as "zero" that there is an error. I have attached a screenshot for reference. Id appreciate any guidance you can offer! Thanks!!

Michael Culley

Well if there was an error it would come back as blank not a 0 (per your iferror statement).

Earlier you said it worked perfectly. Did anything change from then to now?

Bigsby

I thought it was working correctly because this individual didnt have any values in the cells I am wanting to add, so I assumed it was working since the value came back as "zero" I filled in all the cells with values and realized I am still getting a "zero" for some reason.