simplify a large formula

  • Thread starter Thread starter Centauro
  • Start date Start date
C

Centauro

hi i new to this so don't be to hard on me :) I have to sum several cell whit
this formula on it,
=(IF(C13=0,0,AC1)+IF(D13=0,0,AC2)+IF(F13=0,0,AC2))*(IF(OR(N13="P",N13="M"),1,0)+IF(OR(O13="P",O13="M"),1,0)+IF(OR(P13="P",P13="M"),1,0)+IF(OR(Q13="P",Q13="M"),1,0)+IF(OR(R13="P",R13="M"),1,0)+IF(OR(S13="P",S13="M"),1,0)+IF(OR(T13="P",T13="M"),1,0)+IF(OR(U13="P",U13="M"),1,0)+IF(OR(V13="P",V13="M"),1,0)+IF(OR(W13="P",W13="M"),1,0)+IF(OR(X13="P",X13="M"),1,0)+IF(OR(Y13="P",Y13="M"),1,0))

and then

=SUM(AE13:AE24)

tks in advance
 
T. Valko said:
Try this:

=((C13<>0)*AC1+(D13<>0)*AC2+(F13<>0)*AC2)*SUM(COUNTIF(N13:Y13,{"p","m"}))
Click to expand...

greate works!! now this one

=IF(F13=0,0,35)+IF(F14=0,0,35)+IF(F15=0,0,35)+IF(F16=0,0,35)+IF(F17=0,0,35)+IF(F18=0,0,35)+IF(F19=0,0,35)+IF(F20=0,0,35)+IF(F21=0,0,35)+IF(F22=0,0,35)+IF(F23=0,0,35)
 
Well, since I don't know what the possible entries can be here's a generic
approach:

=SUMPRODUCT(--(F13:F23<>0))*35
 
Great that make my final formula

=((C13<>0)*AC1+(D13<>0)*AC2+(F13<>0)*AC2)*SUM(COUNTIF(N13:Y13,{"p","m"}))+((C14<>0)*AC1+(D14<>0)*AC2+(F14<>0)*AC2)*SUM(COUNTIF(N14:Y14,{"p","m"}))+((C15<>0)*AC1+(D15<>0)*AC2+(F15<>0)*AC2)*SUM(COUNTIF(N15:Y15,{"p","m"}))+((C16<>0)*AC1+(D16<>0)*AC2+(F16<>0)*AC2)*SUM(COUNTIF(N16:Y16,{"p","m"}))+((C17<>0)*AC1+(D17<>0)*AC2+(F17<>0)*AC2)*SUM(COUNTIF(N17:Y17,{"p","m"}))+(SUMPRODUCT(--(F13:F23<>0))*35)

i put from row 13 to 17 but in reality willbe from 13 to 23
 
If you could explain what you're wanting to do we can probably shorten that
formula as well.

What kind of data is entered in columns C, D and F?

What kind of data is entered in F13:F23 ?

What's in AC1 and AC2 ?

You'd be surprised what we can do when we know the details!

--
Biff
Microsoft Excel MVP


news:[email protected]...
 
T. Valko said:
If you could explain what you're wanting to do we can probably shorten that
formula as well.

What kind of data is entered in columns C, D and F?

What kind of data is entered in F13:F23 ?

What's in AC1 and AC2 ?

You'd be surprised what we can do when we know the details!
Click to expand...


this is a presence table to determin the amount to pay
basicly the people enter a "X" on this colums C, D and F the value is in AC1
and AC2 (9 and 19) also if there are a "X" on F13:F23 a prime of 35 is ad,
N14:Y14 are the days of attendance they can be a "P","M" or "F" (p and m = 1,
f = 0)

Tks
 
Ok, I *think* this does what you want. Still long, but shorter overall:

=SUMPRODUCT((C13:C23="x")*(ISNUMBER(MATCH(N13:Y23,{"p","m"},0))))*AC1+SUMPRODUCT((D13:D23="x")*(ISNUMBER(MATCH(N13:Y23,{"p","m"},0))))*AC2+SUMPRODUCT((F13:F23="x")*(ISNUMBER(MATCH(N13:Y23,{"p","m"},0))))*AC2+COUNTIF(F13:F23,"x")*35
 
Back
Top