| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Multiple Criteria in different ranges.
"John" wrote: I have a similar problem however, I am using text in all my colums and would like to sum how many times a specific name appears by date. For example: Column A: Date (MM/DD/YY) Column B: Day of the week (Monday) Column C: An employee name (Smith) How can I have a formula to search Column A: "Date", Column B: "Day of the week", and Column C: "An employee name" and then return a "sum of" How many times did "Smith" work on "Monday" in January? Can someone help? "MrAcquire" wrote: Sorry, there was a typo. =SUMPRODUCT((A2=MONDAY!$A$2:$A$50000)*(MONDAY!$D$2 :$D$500007)*($D$2:$D$50000)) "MrAcquire" wrote: SUMPRODUCT is an excellent function for single (instead of SUMIF) or multiple criteria queries. For example, for your multi criteria query, try =SUMPRODUCT((A2=MONDAY!$A$2:$A$50000)*(MONDAY!$D$2 :$D$50000)*($D$2:$D$50000)) What's it doing? Asterisks separate the argument. The first argument says choose all cells in column A of MONDAY with the value of cell A2 in PLANT, i.e, 380085. The second argument says choose all cells with in column D of MONDAY with a value 7. If you didn't put in the third argument, your result would be a count of the records that meet this criteria where both are true (i.e., 2). The third argument says sum all the cells of the rows that meet criteria 1 & 2 (15.97). " wrote: I have a multiple sheet workbook. I have a sumif formula that looks at a range in one worksheet, and if it matches a value in worksheet # 2, sums a predescribed range. Duh, just a standard sumif. Now I need to conduct the same range and criteria search, the conduct an IF function on another column and only SUMIF both criteria are met. Example Sheet = MONDAY A B C D 1 Finance No Oper Work Hrs Overtime Hrs 2 380085 7000 8.02 8 3 380085 2100 7.97 7.97 4 380085 100 7.32 0.63 5 380085 100 0 0 6 380085 100 2.14 0 7 380085 100 4.81 4.81 8 381689 7420 7.08 7.08 9 384851 2290 7.01 7.01 10 389225 2410 7.36 7.36 Sheet = Plant A B C D E 1 FIN # OT POT HRS SDO 2 380085 268.65 6.06 2764.03 I need a SUMIF in the Plant sheet cell E2 that meets both criteria =SUMIF(MONDAY!$A$2:$A$50000,A$2 and =SUMIF(MONDAY!$D$2:$D$50000,"7" I need it to meet BOTH criteria to be summed. In this instance, the sum answer I need is 15.97, just the sum of D2 and D3 because only lines 2 and 3 meet both criteria, the value in column A matches the value in the Plant Sheet A2 and the value in column D is greater than 7. Any assistance would be greatly appreciated. Don |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif with 2 ranges & 2 criteria | Excel Worksheet Functions | |||
| Calculate a SUMIF if criteria is between 2 date ranges | Excel Worksheet Functions | |||
| How to SUMIF multiple ranges? | Excel Discussion (Misc queries) | |||
| SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
| SumIF Multiple Ranges | Excel Worksheet Functions | |||
Threaded Mode
