SUMPRODUCT question

[3G]

HI there-

I have the following formula:


=SUMPRODUCT(([@Status]="Approved")*(([@[System Solution]]=FIND("Software",[@[System Solution]],1))))


It's working, as I'm getting "VALUE" for those that do not have the value "Software" in the System Solution column, and a "0" for the ones that do. However, I'm getting a "0" for those that do have Software. I thought that if it evaluated correctly, it'd show a "1"?

 

[Faseeh]

Hi 3G,


What is the purpose of this formula?? What do you want to do?

 

[Luke M]

This

(([@[System Solution]]=FIND("Software",[@[System Solution]],1))))

will always evaluate to 0 or an error.


Why?

The FIND function either outputs a number indicating position of found text, or the #VALUE error if search_text is not found. You already correctly saw that the whole formula then spits out an error if search text isn't found.

However, let's say the text was found, and FIND gives you a number (let's assume 7). Your formula then compares that output to the contents of the column it just searched. Since we knot you're looking for a string, the value in the column will never equal a number, thus evaluating to FALSE. When this gets multiplied by the other array, it gets turned into a 0.


As Faseeh said, it would probably be best if you said what you're trying to accomplish, and we can work from there to find the best formula.

 

[3G]

Much appreciated guys. My data was as follows:

[pre]

A          |                     B
Status     |                  Packages
Approved   |         Firewall, Consulting Services
Approved   |         Software, Firewall, Consulting Services
Submitted  |         Software,Consulting Services
Pending    |         Software, Firewall, Consulting Services
...                            ...

[/pre]
So, all I was trying to do was to validate that the status was Approved, and, that the word "Software" was in column B.

 

[Faseeh]

Hi 3G,


your data in A1:B7,

[pre]

Status	        Packages
Approved	Firewall, Consulting
Submitted	Software
Pending	        Firewall
Approved	Software, Firewall, Consulting
Submitted	Consulting
Pending	        Software, Firewall, Consulting[/pre]

use:


=SUMPRODUCT(($A$2:$A$7="Approved")*(IFERROR(FIND("Software",$B$2:$B$7),0)>=1)*1)

Press Ctrl+Shift+Enter


Regards,

 

[amrit1186]

Hi 3G


Can you try this formula in Column C.


=IF((IFERROR(SEARCH("Software",B2),0)*(A2="Approved"))>0,"TRUE","FALSE")


Amritansh

 

[Luke M]

If you really want a 1 or 0 output, and it's just 1 line:

=1*(AND([Status]="Approved",ISNUMBER(SEARCH("Software",[System Solution]))))

 

[3G]

Nice. All of 'em. Thanks y'all!!!