IF formula error.

%3CLINGO-SUB%20id%3D%22lingo-sub-2133379%22%20slang%3D%22en-US%22%3EIF%20formula%20error.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2133379%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20help%20me%20the%20below%20details..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(A%3AA%3D%22PCS%20PRODUCT%22%2CA%3AA%3D%22PCS%20SERVICE%22)%2CSUMIFS(C%3AC%2CB%3AB%2C%22HP%22))%3D3227054%3C%2FP%3E%3CP%3E%3DIF(OR(A%3AA%3D%22ECS%20SERVICE%22%2CA%3AA%3D%22ECS%20PRODUCT%22)%2CSUMIFS(C%3AC%2CB%3AB%2C%22HP%22))%3D3227054%2C%3C%2FP%3E%3CP%3Ei%20choose%20the%20product%20ECS%20%26amp%3B%20PCS%2C%20but%20values%20should%20be%20different%20but%20when%20i%20am%20trying%20the%20formula%2C%20still%20i%20am%20getting%20same%20error..%2C%20here%20i%20attached%20the%20my%20file%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20also%20i%20have%20to%20use%20IF(logical%20test1%2C%20IF(logical%20test2%2C%20SUMIF(range%2C%20criteria1%2Ccriteria1%20)))%2C%20please%20help%20me..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2133379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Dear All,

 

Kindly help me the below details..

 

=IF(OR(A:A="PCS PRODUCT",A:A="PCS SERVICE"),SUMIFS(C:C,B:B,"HP"))=3227054

=IF(OR(A:A="ECS SERVICE",A:A="ECS PRODUCT"),SUMIFS(C:C,B:B,"HP"))=3227054,

i choose the product ECS & PCS, but values should be different but when i am trying the formula, still i am getting same error.., here i attached the my file 

And also i have to use IF(logical test1, IF(logical test2, SUMIF(range, criteria1,criteria1 ))), please help me..

 

2 Replies

@SUDHAKAR-FRONTIER 

 

OR(A:A="PCS PRODUCT",A:A="PCS SERVICE") returns a single TRUE/FALSE value, not an array. Since there is at least one cell in column A that contains PCS PRODUCT, it returns TRUE. The same goes for OR(A:A="ECS SERVICE",A:A="ECS PRODUCT"). So both formulas are equivalent to =SUMIFS(C:C,B:B,"HP").

 

Try

 

=SUM(SUMIFS(C:C,A:A,{"PCS PRODUCT","PCS SERVICE"},B:B,"HP"))

 

or

 

=SUMIFS(C:C,A:A,"PCS *",B:B,"HP")

Thank you so much Hans,

Now its working fantastic...