Compare values

[Navhonda84]

Hi Guys,
This is my first post, need some help on a small formula.
What i need is to compare a specific term from a row and then add the relavent values along that term and display.

I have a list of Categories, CAT1, CAT2, CAT3 and CAT4 and I have tasks against it and allocated hours
What I want is to add the hours against each CAT and populate it in a column.
So even if i add new tasks at the bottom it shud automatically pick up the CAT and add to the final value....
Does that make sense...
I am attaching a simple file.. please help...
I think i need an IF formula, like (relates to the file attached)

=IF(h3:h17=CAT1,sum(etc....), thas were i am stuck

I will write the formula like I want the solution to be,

=IF(h3:h17=CAT1, sum(4 columns along that CAT), "")
The same for all the CAT's..

Does this make any sense...????
And I am using Office 2003.... PLease Help

[Maistrye]

Hi,

The following formula should do what you're wanting. I wasn't sure where you intended to put it, so it's assumed to be in cell B3.

=SUMPRODUCT(--($H$3:$H$17=A2),$I$3:$I$17+$J$3:$J$17+$K$3:$K$17+$L$3:$L$17)  

This same formula can be copied from B3 and pasted in B7, B11, B15.

---------------

An alternate solution if you're comfortable with named ranges is to specify a named range called Tasks for G3:G17.

Then you can use the following formula and it will mean you only have to adjust the named range Tasks when you add more rows. (Potentially by making it a dynamic named range)

=SUMPRODUCT(--(OFFSET(Tasks,0,1)=A2),OFFSET(Tasks,0,2)+OFFSET(Tasks,0,3)+OFFSET(Tasks,0,4)+OFFSET(Tasks,0,5))  

This second one is more useful in general, but is slightly slow when you have lots of data. Someone may have a better idea to make it quicker.

S

[Navhonda84]

thankyou very much for your hep Maistyre
now one more question, if I write on cell;
I1 10
J1 10
K1 20
L1 30
Now I need is, for CAT1 add all the hours that are paid at rate 10, rate 20 and rate 30 sepertly.
So baically a split dwn of the hours for each cat according to pay

Thanks in advance guys

[Maistrye]

Hi,

Here's what you can put in, both ways.

Method 1: (Adjusting the formulas from before)

C2: =I2  <= Copy to D2, E2, F2 
 
B3: =SUM(C3:F3) 
C3: =SUMPRODUCT(--($H$3:$H$17=$A2),I$3:I$17)   <= Copy to D3, E3, F3 
 
Now copy B3:F3 to B7, B11, B15.  

Method 2: (Using named range, adjusting formulas from before)

C2: =I2  <= Copy to D2, E2, F2 
 
B3: =SUM(C3:F3) 
C3: =SUMPRODUCT(--(OFFSET(Tasks,0,1)=$A2),OFFSET(Tasks,0,2)) 
D3: =SUMPRODUCT(--(OFFSET(Tasks,0,1)=$A2),OFFSET(Tasks,0,3)) 
E3: =SUMPRODUCT(--(OFFSET(Tasks,0,1)=$A2),OFFSET(Tasks,0,4)) 
F3: =SUMPRODUCT(--(OFFSET(Tasks,0,1)=$A2),OFFSET(Tasks,0,5)) 
 
Now copy B3:F3 to B7, B11, B15.  

Also, if you're familiar with PivotTables, you may want to consider using a PivotTable for this instead. Depending on your end goal with your spreadsheet, they may produce a simpler result (although the initial setup can be somewhat complicated).

S