simplify a large formula

[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]

Try this:

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

 

[Centauro]

Try this:

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

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)

 

[T. Valko]

Well, since I don't know what the possible entries can be here's a generic
approach:

=SUMPRODUCT(--(F13:F23<>0))*35

 

[Centauro]

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

 

[T. Valko]

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]...

 

[Centauro]

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!

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

 

[T. Valko]

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((D1323="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