G
Guest
The idea behind this is very simple, but applying it to Excel is proving
quite troublesome and i can't find a solution.
Overview:
Column A - List of peoples names, 7 or so different names in total (Job
Managers). (Each name can appear any number of times. i.e. more than once).
Column B - Dollar value - fees. (Fee sent to a client which they owe to us).
Column C - The date the fees were paid by the client/recieved by us.
Each row is a single entry. i.e. all values correspond.
Sample data
COL A COL B COL C
ROW 1 Name 1 $x,xxx.xx 01-Jun-04
ROW 2 Name 3 $x,xxx.xx 02-Jun-04
ROW 3 Name 2 $x,xxx.xx 01-Jun-04
ROW 4 Name 2 $x,xxx.xx 07-Jun-04
ROW 5 Name 1 $x,xxx.xx 01-Jun-04
The Problem:
I am trying to get the total cash recieved. For a criteria by month, then
within that month, by job manager. That way i can neatly display a summary in
a separate worksheet, away from the lengthy worksheet with all the fees
recieved. Showing a list of each job managers name then next to their name
the amount of fees they recieved for that month.
Eg.
IF
Month = "June"
AND
Job Manager = "Name 1"
Then, SUM
fees recieved, else 0.
Given the syntax:
=IF(logical_test, [value_if_true], [value_if_false])
=SUMIF(range, criteria, [sum_range])
Notes:
For the following, 'Ranges' have been simplified for easier explanation.
- I tried nesting a SUMIF in an IF statement. But the IF statement only
targets one cell and not a range.
=IF(Column C=('Jun'), SUMIF(Column A, "Name 1", Column B), 0)
- I tried nesting a SUMIF in a SUMIF statement. I couldnt get the syntax to
work. Either it will never work or i just cant troubleshoot the error myself.
=SUMIF(Column C, "Jun", (SUMIF(Column A, "Name 1", Column B))
Not sure on what other functions i could use to give me a solution.
Thanks for the feedback and any help you may have to offer.
Don
quite troublesome and i can't find a solution.
Overview:
Column A - List of peoples names, 7 or so different names in total (Job
Managers). (Each name can appear any number of times. i.e. more than once).
Column B - Dollar value - fees. (Fee sent to a client which they owe to us).
Column C - The date the fees were paid by the client/recieved by us.
Each row is a single entry. i.e. all values correspond.
Sample data
COL A COL B COL C
ROW 1 Name 1 $x,xxx.xx 01-Jun-04
ROW 2 Name 3 $x,xxx.xx 02-Jun-04
ROW 3 Name 2 $x,xxx.xx 01-Jun-04
ROW 4 Name 2 $x,xxx.xx 07-Jun-04
ROW 5 Name 1 $x,xxx.xx 01-Jun-04
The Problem:
I am trying to get the total cash recieved. For a criteria by month, then
within that month, by job manager. That way i can neatly display a summary in
a separate worksheet, away from the lengthy worksheet with all the fees
recieved. Showing a list of each job managers name then next to their name
the amount of fees they recieved for that month.
Eg.
IF
Month = "June"
AND
Job Manager = "Name 1"
Then, SUM
fees recieved, else 0.
Given the syntax:
=IF(logical_test, [value_if_true], [value_if_false])
=SUMIF(range, criteria, [sum_range])
Notes:
For the following, 'Ranges' have been simplified for easier explanation.
- I tried nesting a SUMIF in an IF statement. But the IF statement only
targets one cell and not a range.
=IF(Column C=('Jun'), SUMIF(Column A, "Name 1", Column B), 0)
- I tried nesting a SUMIF in a SUMIF statement. I couldnt get the syntax to
work. Either it will never work or i just cant troubleshoot the error myself.
=SUMIF(Column C, "Jun", (SUMIF(Column A, "Name 1", Column B))
Not sure on what other functions i could use to give me a solution.
Thanks for the feedback and any help you may have to offer.
Don