Nesting If Statement? or another way

[tclark14]

Hello, I am desperate for some help here.

I have 3 different proj codes and several sub accounts (all not displayed). I have categories set up which include several of the different sub accounts. What I need is a formula that will sum corresponding amts in column G if the projcode in C is 201, and if the subacct in F is 101, 102, and 114. If you could help with this I would appreciate it so much. THANKS. Terry

C D E F G
PROJCODE PROJNAME GENACCT SUBACCT NETAMT
201 WILSON COAL A-24 33 101 57300
201 WILSON COAL A-24 33 102 3600
203 Shonk #36 33 101 52100
203 Shonk #46 33 102 2000
205 Poca B12 33 101 63100
205 Poca B12 33 102 4900
205 Poca B12 33 104 15000
201 WILSON COAL A-24 33 113 0
201 WILSON COAL A-24 33 114 1400

[tclark14]

that the columns wouldn't stay as I had typed them.

First column is the 3-dgit dumber, 2nd is text , 3rd are all 033's, 4th is again a 3-dgit number, and the last is an amount.

THanks!

[richardreye]

something like this should work

=SUMPRODUCT(--(C1:C10=201),(--(F1:F10=101) + (--(F1:F10=102)) + (--(F1:F10=114))),(G1:G10))

Adjust C1:C10 etc to the range of data you have.

HTH

[tclark14]

it wants to change the ,(g11:g10)) to be *(g1:g10))

[richardreye]

Did you cut and paste it in? I've tested it on Excel 2000 & 2003 nad it accepts the formula.

[tclark14]

I did not copy and paste the first time, I did and it works - thanks for your help!

Terry

[tclark14]

if the cell references c2:c10 on another worksheet in the workbork instead of the current one (called viewfmt), where would I place the viewfmt!


Canout you tell me how to use =COUNT, but not to count any duplicates?

Thanks!

[Landyman]

But what does the "--" do in these formulas, I see them used quite often, but never know how they work

Cheers