Calculation problem

[austenr]

In the attached WB, in column E, a 1 is entered if the student is a foreign student. This triggers the formula in Column AB to populate that cell with the word Foreign (it defaults to "US"). Now notice the formula in D4 and D5. I need a formula that calculates the same way D4 and D5 do for cells F4 and F5. How can you do the kind of calculation done in D4 and D5 because you cannot divide by a text string? I know you cannot do that but is there a way to do it another way? Thanks

[Bob Phillips]

=SUMPRODUCT(--($AB$9:$AB$128=$E4),$V$9:$V$128,$F$9:$F$128)

But I also don't get the formula in V9

=(COUNTIF(S9:U9, ">0"))+(COUNTIF(S9:U9, "<0"))

and W9

=SUM(S9:U9)

because as far as I can see, T9 and U9 are not numbers.

[austenr]

Me either. Was just asked to make these modifications. So your formual goes in both F4 and F5?

[Bob Phillips]

It goes in F4, and copied down to F5.

[austenr]

Works like a charm. The formula king!!!

[austenr]

Hold on, Im not seeing the formula populating the cell with any numbers. Am I missing something here?

[Bob Phillips]

Because all of those numbers in column V are all zero, which are zero because S is zero.

[austenr]