Calculating a sum in one column based upon the criteria in two other columns

[Lsullivan1360]

Hi,
I have a scenario that I am having difficulty figuring out. I am new to the extra functions in excel and could use some advice.

I have a sheet that Column J is User name, Column L is a dollar amount. I have a small grid/table of the user names in which I have Column S = Rep Name, Column T, Loss Count (=COUNTIF($J$2:$J$201, S2) ), Column U Loss Count < $500 (=COUNTIFS($J$2:$J$200,$S2,$L$2:$L$200,"<500" ) ), Column V Loss > $500 (=COUNTIFS($J$2:$J$200,$S2,$L$2:$L$200,">=500" ) ) and column W as total loss amount (=SUMIF($J$2:$J$200,$S2, $L$2:$L$200) ).

What I am having problems with is in Column X I have Sum of Total Losses < $500 for the rep and Column Y is Sum of Total Losses > $500.
The cells need to look at rep in column s, compare to line item on the spreadsheet to see the rep name, get the loss for over or under $500 and them sum those in the appropriate column for that rep.

Any assistance or suggestions would greatly appreciated. I have been pulling out my hair on figuring this one out.

thank you,
L

 

[jasonb75]

A little confused as to what is where with your description, not sure if column X is the data source or the location for the formula. Does this help?

=SUMIFS($L$2:$L$200,$J$2:$J$200,$S2,$L$2:$L$200,"<=500")

 

[Lsullivan1360]

Thank you Jasonb75, that did exactly what I was trying to accomplish. I was having problems with the sumifs function. I very much appreciate your help.

L

A little confused as to what is where with your description, not sure if column X is the data source or the location for the formula. Does this help?

=SUMIFS($L$2:$L$200,$J$2:$J$200,$S2,$L$2:$L$200,"<=500")