formula question

1996mustang

I have a sheet that has columns of Color Status, As Sold Budget, and Status. Color Status equals either PURPLE, GREEN, YELLOW or RED. As Sold Budget is a column of dollars. Status is either At Gosiger, Close, SAT Pending, Shipped Debug, Need Final Docs, or On Hold.

Conditions of the formula...

If the Status is not equal to "Close" and the Color Status is "Purple", I want sum the As sold Budget. My Formula is as below.

=IF(AND(Status6:Status58, <>"Close", [Color Status]6:[Color Status]58, ="PURPLE", SUM([As Sold Budget]6:[As Sold Budget]58)))

The return is (Incorrect Argument)

Please advise on what I am doing wrong.

Regards,

DS

1996mustang

Bassam,

Your suggestion worked. Thank you for your help!

DS😀

1996mustang

I want to put the formula and copy as follows:

PURPLE 7[As Sold Budget]59 (CEll 74)

GREEN 7[As Sold Budget]59 (Cell 75)

YELLOW 7[As Sold Budget]59 (Cell 76)

RED 7[As Sold Budget]59 (Cell 77)

Then move to Current Budget, Current Est, Current Sales.

Regards,

Bassam Khalil

@1996mustang 

Excellent 😀

Bassam Khalil

Hi @1996mustang

Hope you are fine, please add a copy or screenshot after removing any sensitive data.

Bassam Khalil

@1996mustang 

Try the following formula

=SUMIFS([As Sold Budget]6:[As Sold Budget]58, [Color Status]6:[Color Status]58, @cell = "PURPLE", Status6:Status58, @cell <> "Close")

1996mustang

=SUMIFS([AS Sold Budget]6:[As Sold Budget]58,[Color Status]6:[Color Status ]58,@cell="PURPLE",Status6:Status58,@cell<>"Close")

Bassam,

I enter the formula and I received a #UNPARSEABLE. Any Suggestions?

Regards,

ds

Bassam Khalil

@1996mustang

Please check the column names if it's as in my formula, or if you can share me as an admin on a copy of your sheet (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

1996mustang

Bassam,

I have shared the sheet with you. Please review and advise.

ds

1996mustang

Bassam,

Your suggestion worked. Thank you for your help!

DS😀

1996mustang

Great help! 5 stars

1996mustang

If I copy the formulas to another cell, it doesn't work. For example, I want to copy the formula to [As Sold Budget]74 and etc.

Thoughts?

Bassam Khalil

@1996mustang

Please share again to fix it for you

Bassam Khalil

@1996mustang

The formula for As Sold Sales is fixed please check it

=SUMIFS([As Sold Sales]7:[As Sold Sales]59, [Color Status]7:[Color Status]59, @cell = [As Sold Budget]@row, Status7:Status59, @cell <> "Close")

1996mustang

I want to put the formula and copy as follows:

PURPLE 7[As Sold Budget]59 (CEll 74)

GREEN 7[As Sold Budget]59 (Cell 75)

YELLOW 7[As Sold Budget]59 (Cell 76)

RED 7[As Sold Budget]59 (Cell 77)

Then move to Current Budget, Current Est, Current Sales.

Regards,

1996mustang

Bassam,

That worked, thank you for your help!

Regards,

ds😁

Bassam Khalil

@1996mustang 

Excellent 😀