Problem with sumproduct

[siulonbow]

In my spreadsheet, I used the formula in below , and the return amount is off. I am thinking it might be picking up other criteria as well. the formula that is use is as follow:

=(SUMPRODUCT(ISNUMBER(SEARCH("Ridgecrest * tier 1",$M$2:$M$65536))*($Y$2:$Y$65536=$AG76)*($O$2:$O$65536<1978)*($P$2:$P$65536="Single Family"),$Q$2:$Q$65536))/1000

I am thinking that the ISNUMBER(SEARCH("Ridgecrest * tier 1") is picking up the "Ridgecrest * tier 1+". How would I be able to have the excel just pick the ridgecrest tier 1 only? Thanks

[Ron Coderre]

It might be a simple as adding a space to your criteria and the cells being tested.

Try this:

=(SUMPRODUCT(ISNUMBER(SEARCH("Ridgecrest * tier 1 ",$M$2:$M$65536&" "))*
($Y$2:$Y$65536=$AG76)*($O$2:$O$65536<1978)*($P$2:$P$65536="Single Family"),
$Q$2:$Q$65536))/1000

Does that work?

[daddylonglegs]

What sort of entries do you have in column M? Do these actually contain asterisks like * or are you using that as a "wildcard"?

SEARCH function treats * as a wildcard so it would also count any entry like "Ridgecrest xyzxyz tier 1". If you're looking for a literal asterisk then precede with a ~, i.e.

"Ridgecrest ~* tier 1 "

[siulonbow]

Please let me be more clear on my question. The problem I am having right now is my sumproduct is picking up unnecessary items. When I run use pivot table with the same criteria, the number that I got doesn't match with the sumproduct.


My formula has included 2 similar criterira because the N column has 2 different type of 1 tier that I would want to pick out. (1. "Ducted Evaporative * 1 tier" and 2. "Ducted Evaporative * X-1 Tier") Can I combine the 2 into one sumproduct?

=(SUMPRODUCT(ISNUMBER(SEARCH("Ducted Evaporative * 1 tier",$N$2:$N$65536))*($Y$2:$Y$65536=$AG75)*($O$2:$O$65536<1978)*($P$2:$P$65536="Single Family"),$Q$2:$Q$65536))/1000+((SUMPRODUCT(ISNUMBER(SEARCH("Ducted Evaporative * X-1 tier *",$N$2:$N$65536))*($Y$2:$Y$65536=$AG75)*($O$2:$O$65536<1978)*($P$2:$P$65536="Single Family"),$Q$2:$Q$65536))/1000)

The 2 measures that I would like the sumproduct to pick out from column N are:
1) Ducted Evaporative Cooler Single Stage 1 Tier ADOBE ....
2) Ducted Evaporative Cooler Single Stage X-1 Tier ....

[olasa]

You could add: &" " or &A1 where you put " "
SEARCH("Ridgecrest * tier 1"&" ",$M$2:$M$65536&" ")
HTH
Ola


...I see Ron's and my suggestions are excactly alike...might be worth something.