help on conditional

[Danbmarine]

Here is what I have,

=SUM(IF(Sheet1!D9="JF",Sheet1!E9,0)+IF(Sheet1!D10="JF",Sheet1!E10,0)+IF(Sheet1!D11="JF",Sheet1!E11,0)+IF(Sheet1!D12="JF",Sheet1!E12,0)+IF(Sheet1!D13="JF",Sheet1!E13,0)+IF(Sheet1!D14="JF",Sheet1!E14,0)+IF(Sheet1!D15="JF",Sheet1!E15,0)+IF(Sheet1!D16="JF",Sheet1!E16,0)*IF(Sheet1!D17="JF",Sheet1!E17,0)+IF(Sheet1!D18="JF",Sheet1!E18,0)+IF(Sheet1!D19="JF",Sheet1!E19,0)+IF(Sheet1!D20="JF",Sheet1!E20,0)+IF(Sheet1!D21="JF",Sheet1!E21,0)+IF(Sheet1!D22="JF",Sheet1!E22,0)+IF(Sheet1!D23="JF",Sheet1!E23,0)+IF(Sheet1!D24="JF",Sheet1!E24,0)+IF(Sheet1!D25="JF",Sheet1!E25,0))

is there a simpler formula to use so I dont have to add a condition every
time I add a row in sheet1?

Thanks,
Dan

[JE McGimpsey]

One way:

=SUMIF(D9:D100,"JF",E9:E100)

In article <[email protected]>,
"Danbmarine" <[email protected]> wrote:

> Here is what I have,
>
> =SUM(IF(Sheet1!D9="JF",Sheet1!E9,0)+IF(Sheet1!D10="JF",Sheet1!E10,0)+IF(Sheet1
> !D11="JF",Sheet1!E11,0)+IF(Sheet1!D12="JF",Sheet1!E12,0)+IF(Sheet1!D13="JF",Sh
> eet1!E13,0)+IF(Sheet1!D14="JF",Sheet1!E14,0)+IF(Sheet1!D15="JF",Sheet1!E15,0)+
> IF(Sheet1!D16="JF",Sheet1!E16,0)*IF(Sheet1!D17="JF",Sheet1!E17,0)+IF(Sheet1!D1
> 8="JF",Sheet1!E18,0)+IF(Sheet1!D19="JF",Sheet1!E19,0)+IF(Sheet1!D20="JF",Sheet
> 1!E20,0)+IF(Sheet1!D21="JF",Sheet1!E21,0)+IF(Sheet1!D22="JF",Sheet1!E22,0)+IF(
> Sheet1!D23="JF",Sheet1!E23,0)+IF(Sheet1!D24="JF",Sheet1!E24,0)+IF(Sheet1!D25="
> JF",Sheet1!E25,0))
>
> is there a simpler formula to use so I dont have to add a condition every
> time I add a row in sheet1?
>
> Thanks,
> Dan

[Bob Phillips]

This is a bit simpler,

=SUMPRODUCT(--(Sheet1!D9:D25="JF"),Sheet1!E9:E25)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Danbmarine" <[email protected]> wrote in message
news:[email protected]...
> Here is what I have,
>
>
=SUM(IF(Sheet1!D9="JF",Sheet1!E9,0)+IF(Sheet1!D10="JF",Sheet1!E10,0)+IF(Shee
t1!D11="JF",Sheet1!E11,0)+IF(Sheet1!D12="JF",Sheet1!E12,0)+IF(Sheet1!D13="JF
",Sheet1!E13,0)+IF(Sheet1!D14="JF",Sheet1!E14,0)+IF(Sheet1!D15="JF",Sheet1!E
15,0)+IF(Sheet1!D16="JF",Sheet1!E16,0)*IF(Sheet1!D17="JF",Sheet1!E17,0)+IF(S
heet1!D18="JF",Sheet1!E18,0)+IF(Sheet1!D19="JF",Sheet1!E19,0)+IF(Sheet1!D20=
"JF",Sheet1!E20,0)+IF(Sheet1!D21="JF",Sheet1!E21,0)+IF(Sheet1!D22="JF",Sheet
1!E22,0)+IF(Sheet1!D23="JF",Sheet1!E23,0)+IF(Sheet1!D24="JF",Sheet1!E24,0)+I
F(Sheet1!D25="JF",Sheet1!E25,0))
>
> is there a simpler formula to use so I dont have to add a condition every
> time I add a row in sheet1?
>
> Thanks,
> Dan