simplify a large formula
 

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(S1 3="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

simplify a large formula
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Centauro" wrote in message
...
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(S1 3="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

simplify a large formula
 

"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP

greate works!! now this one

=IF(F13=0,0,35)+IF(F14=0,0,35)+IF(F15=0,0,35)+IF(F 16=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)

simplify a large formula
 

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

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

--
Biff
Microsoft Excel MVP


"Centauro" wrote in message
...


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP

greate works!! now this one

=IF(F13=0,0,35)+IF(F14=0,0,35)+IF(F15=0,0,35)+IF(F 16=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)

simplify a large formula
 

Great that make my final formula

=((C13<0)*AC1+(D13<0)*AC2+(F13<0)*AC2)*SUM(COUN TIF(N13:Y13,{"p","m"}))+((C14<0)*AC1+(D14<0)*AC2 +(F14<0)*AC2)*SUM(COUNTIF(N14:Y14,{"p","m"}))+((C 15<0)*AC1+(D15<0)*AC2+(F15<0)*AC2)*SUM(COUNTIF( N15:Y15,{"p","m"}))+((C16<0)*AC1+(D16<0)*AC2+(F1 6<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

simplify a large formula
 

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


"Centauro" wrote in message
...
Great that make my final formula

=((C13<0)*AC1+(D13<0)*AC2+(F13<0)*AC2)*SUM(COUN TIF(N13:Y13,{"p","m"}))+((C14<0)*AC1+(D14<0)*AC2 +(F14<0)*AC2)*SUM(COUNTIF(N14:Y14,{"p","m"}))+((C 15<0)*AC1+(D15<0)*AC2+(F15<0)*AC2)*SUM(COUNTIF( N15:Y15,{"p","m"}))+((C16<0)*AC1+(D16<0)*AC2+(F1 6<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

simplify a large formula
 

"T. Valko" wrote:

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


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

simplify a large formula
 

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")*(ISNU MBER(MATCH(N13:Y23,{"p","m"},0))))*AC2+SUMPRODUCT( (F13:F23="x")*(ISNUMBER(MATCH(N13:Y23,{"p","m"},0) )))*AC2+COUNTIF(F13:F23,"x")*35

--
Biff
Microsoft Excel MVP


"Centauro" wrote in message
...


"T. Valko" wrote:

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


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