Trying to find a formula that will work

[Marsha Layton]

Hi Chandoo,
I’m having a lot of trouble writing a formula. Or should I say everything I’ve written comes back wrong.
so this is what I’m trying to do;
Column B has Names
Column C is just telling me if they still work for out company
Column D has the tier level in it (T1,T2,T3)
Column E has equipment #
Column F has Die #
Column G has hours need to run the job before they can test out
Column H has total hours running that job
Column I has date
Column J has points
Column K as date
Column L has points
All other columns go date then points
So what I’m trying do is;
Look for a name
then look for a tier level
then add the points in j, l, n, p and so on.
When I use sumproduct for just looking up the name and level and 1 points column it works great but then I try to add the other columns and I get a N/A or Name
Here is the last one I wrote tried many ways but still not doing what I need.
=sumproduct(–B:B=K2),–(D:D=K3),(J:J,L:L))
Please help
thanks
Reply

• 
  Hui... says:
  March 20, 2015 at 1:34 am
  @Baby734
  Try:
  =sumproduct((B:B=K2)*(D:D=K3)*(J:J)*(L:L))
  I’d also recomend limiting the column sizes
  so: =sumproduct((B1:B1000=K2)*(D1:D10000=K3)*(J1:J1000)*(L1:L1000))
  is 1/1000th the size of the whole columns and will be correspondingly faster
  If that doesn’t work

It did not work when I put the other columns in.
I have uploaded a copy of the file. If anyone can help it would be great. thank you

 

[NARAYANK991]

Hi Marsha ,

This formula works ; the problem was your table headers.

=SUMPRODUCT(ISNUMBER(SEARCH("Points Earned",Table2[[#Headers],[Points Earned]:[Notes]]))*(Table2[Name]=$K$2)*(Table2[Tier Lv]=$K$3)*(Table2[[Points Earned]:[Notes]]))

The correct result is 1406 points.

Narayan

 

[eibi]

Marsha,

I'm looking at cell L3 in the sample file you provided.

First observation: You have a typo in the (B17:B656:K2) section of your formula. It should be (B17:B656=K2)

Second observation: You have a typo in one of the ranges in the same formula (AD17:AD336) should be (AD17:AD656)

Third observation: It looks like by using a * operator, you are multiplying the ranges (0.5*0.5*0.5) rather than adding the ranges (0.5+0.5+0.5)

Do this instead: ADD all the ranges (columns J through AT) inside a parenthesis.

=SUMPRODUCT((B17:B656=K2)*(D17:D656=K3)*((J17:J656)+(L17:L656)+(N17:N656)+(P17: P656)+(R17:R656)......)))

I've made these modifications in the attached file.

Does this help?

 

[Marsha Layton]

Marsha,

I'm looking at cell L3 in the sample file you provided.

First observation: You have a typo in the (B17:B656:K2) section of your formula. It should be (B17:B656=K2)

Second observation: You have a typo in one of the ranges in the same formula (AD17:AD336) should be (AD17:AD656)

Third observation: It looks like by using a * operator, you are multiplying the ranges (0.5*0.5*0.5) rather than adding the ranges (0.5+0.5+0.5)

Do this instead: ADD all the ranges (columns J through AT) inside a parenthesis.

=SUMPRODUCT((B17:B656=K2)*(D17:D656=K3)*((J17:J656)+(L17:L656)+(N17:N656)+(P17656)+(R17:R656)......)))

I've made these modifications in the attached file.

Does this help?

It looks like it's going to work. Thank you so much. Thank everyone who helped on this.