IF, SUMIF or AND! how can I do this??

[office]

Do I have an unsolvable problem? I really hope not
Here's the deal:
I have named all three columns that I want to use,
SNCOL = Serial number column (range)
RATE = Rate column 0=normal time, 2=over time (range)
TOTHOURS = Total hours column (range)

I have been trying to do the following:
Every time the same number appears in the SNCOL range and the RATE =0 sum
the TOTHOURS for all instances of that number only.
Then I want to do the same thing with RATE=2

SNCOL RATE TOTHOURS
98 0 2.00
92 2 3.00
87 2 1.50
98 2 2.25
87 0 3.50
98 0 4.00
98 2 5.00
92 2 3.25
98 0 1.00
So the total hours of 98 at rate 2 will be 7.25 and at rate 0 will be 3.00
hours

IF SNCOL=A4&RATE=0,SUM TOTHOURS (but sum ONLY the the hours for 98 when RATE
0 occurs)
(on the sheet that I want to put the formula in, cell A4 has the number 98
in it. cell A5 has the number 92 in it. All occur randomly but never change)
I have been trying to get the proper version of the above formula working
without success.

Any help would be brilliant
Thanx

Rico
Thanx to all, this group is fantastic, a great help!!!!

 

[Cecilkumara Fernando]

I named the range excluding the header row
SNCOL refers to =$A$2:$A$10 so on
then I select the SNCOL range including the header
Data>Filter=Advanced filter>
Unique values copy to range E1
in F1 I put 0 and in G1, 2
in F2 I put the formula
=SUMPRODUCT((SNCOL=$E2)*(RATE=F$1)*TOTHOURS)
and fill it down and right
this is what i got
SNCOL 0 2
98 7 7.25
92 0 6.25
87 3.5 1.5
Did you try pivot table on that data set
It gives the same and grand totals too.
HTH
Cecil

 

[Karen]

You can do an array-entered equation, which allows you to
apply multiple criteria to a conditional sum. Assuming
SNCOL, RATE, and TOTHOURS are column headers for columns
A, B, & C respectively, you can write the equation as such:

{=SUM((A2:A10=98)*(B2:B10=2)*(C2:C10))}

More criteria can be used in a similar manner. If your
criterion is text, use quotes ("") around it (i.e.
A2:A10="Andy"). Cell references work there as well (i.e.
A2:A10=D2). If you wanted the criteria to be connected
with an OR, rather than an AND, you can use + instead of *
(i.e. if A2:A10=98 OR B2:B10=2).

Make sure you enter it is an array (instead of just
hitting enter, hold down the control+shift when hitting
enter - this will generate the brackets {} - don't
actually type those in)

Hope that helps,
Karen