Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple Criteria for SUMIF
I have the following list of info:
!----A----!----B----!----C----!----D----! ! 2/3/05 ! 114 ! 4.5 ! ! ! 2/3/05 ! 114 ! 3.2 ! ! ! 2/3/05 ! 115 ! 5.7 ! ! ! 2/4/05 ! 113 ! 8.0 ! ! ! 2/4/05 ! 114 ! 4.5 ! ! ! 2/4/05 ! 114 ! 4.6 ! ! ! 2/5/05 ! 114 ! 6.7 ! ! This is where Column A is the date worked, B the employee number, and C the amount of hours split into different job types. For example, I need a formula that will look up al of the hours employee 114 worked only on 2/4/05. It would be nice if Excel had an easy SUBTOTAL function that would take multiple conditions. How do you make this work? Currently, I'm using the D column and doing a Concatenate of A and B and doing a SUMIF off of that range. Surely there are better ways. |
#2
|
|||
|
|||
Try this, with the employee number you're looking for entered in E1, and the
date entered in E2: =SUMPRODUCT((A1:A50=E2)*(B1:B50=E1)*C1:C50) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "camerons" wrote in message ... I have the following list of info: !----A----!----B----!----C----!----D----! ! 2/3/05 ! 114 ! 4.5 ! ! ! 2/3/05 ! 114 ! 3.2 ! ! ! 2/3/05 ! 115 ! 5.7 ! ! ! 2/4/05 ! 113 ! 8.0 ! ! ! 2/4/05 ! 114 ! 4.5 ! ! ! 2/4/05 ! 114 ! 4.6 ! ! ! 2/5/05 ! 114 ! 6.7 ! ! This is where Column A is the date worked, B the employee number, and C the amount of hours split into different job types. For example, I need a formula that will look up al of the hours employee 114 worked only on 2/4/05. It would be nice if Excel had an easy SUBTOTAL function that would take multiple conditions. How do you make this work? Currently, I'm using the D column and doing a Concatenate of A and B and doing a SUMIF off of that range. Surely there are better ways. |
#3
|
|||
|
|||
Thanks for the attempt, but unfortunately that did not seem to work either. Sorry for the delayed response, work and life have been hectic. Thanks for trying to help though. |
#4
|
|||
|
|||
When you say "did not seem to work", what exactly do you mean?
Are you getting wrong answers ... no answers ... error messages ??? Since the suggestion was tested on the exact data you posted, your problem could very easily be a simple matter of different "types" of data. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "camerons" wrote in message ... Thanks for the attempt, but unfortunately that did not seem to work either. Sorry for the delayed response, work and life have been hectic. Thanks for trying to help though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SumIf Function using multiple criteria | Excel Worksheet Functions | |||
Sum using multiple criteria | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |