| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Multiple Criteria in different ranges.
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
On Feb 5, 11:45*am, "Don Guillett" wrote:
=sumproduct((MONDAY!$A$2:$A$50000=A$2)*(MONDAY!$D$ 2:$D$500007)*rng to sum) suggest you limit your range to a more meaningful number or use a defined name range that is self adjusting. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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- Hide quoted text - - Show quoted text - Thanks Don, Unless I just don't understand, I used the following formula =SUMPRODUCT((MONDAY!$A$3:$A$50000=A$2)*(MONDAY!$H$ 3:$H$500007)*MONDAY! $H$3:$H$50000) It is returning an answer of 0. As in the example, a manual review of the appropriate data indicated the answer should have been 15.97. What am I doing wrong? I am using the ranges I used because the Monday Sheet has over 40,000 lines of data and chnages from week to week. If there is a better way to define the range, I am open to suggestions. Don |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
Don, I have the same questioin. Did you get your answer?
" 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
The formula looks correct.
Check to see if the "Finance No" on the MONDAY sheet is formatted as text or number and then check to see what it is formatted as on the PLANT sheet. Laura wrote in message ... On Feb 5, 11:45 am, "Don Guillett" wrote: =sumproduct((MONDAY!$A$2:$A$50000=A$2)*(MONDAY!$D$ 2:$D$500007)*rng to sum) suggest you limit your range to a more meaningful number or use a defined name range that is self adjusting. -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ... 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- Hide quoted text - - Show quoted text - Thanks Don, Unless I just don't understand, I used the following formula =SUMPRODUCT((MONDAY!$A$3:$A$50000=A$2)*(MONDAY!$H$ 3:$H$500007)*MONDAY! $H$3:$H$50000) It is returning an answer of 0. As in the example, a manual review of the appropriate data indicated the answer should have been 15.97. What am I doing wrong? I am using the ranges I used because the Monday Sheet has over 40,000 lines of data and chnages from week to week. If there is a better way to define the range, I am open to suggestions. Don |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
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 |
|
#10
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
Assuming that all of your data is really text beginning in row 2, that is the
date is not a serial number, I would start by inserting a helper column A for Month with the formula in A2 (copied down) =LEFT(B2,2) So now your dates start in B2, day of week in C2 (same deal, text and not a formula), and names in D2. To count how many times Smith worked on Mondays in January =SUMPRODUCT((A2:A1000="01")*(C2:C1000="Monday")*(D 2:D1000="Smith")) If your dates are really serial number dates, use =MONTH(B2) instead of =LEFT(B2,2) in your helper column and A2:A1000=1 instead of A2:A1000="01" as the first SUMPRODUCT argument. "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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF Multiple Criteria in different ranges.
Or better yet, forget the helper column,
=SUMPRODUCT((LEFT(B2:B100),3)="01")*(C2:C100="Mon" )*(D2:D100="Smith")) or, if date is a serial number, =SUMPRODUCT((MONTH(B2:B100)=1)*(C2:C100="Mon")*(D2 :D100="Smith")) "MrAcquire" wrote: Assuming that all of your data is really text beginning in row 2, that is the date is not a serial number, I would start by inserting a helper column A for Month with the formula in A2 (copied down) =LEFT(B2,2) So now your dates start in B2, day of week in C2 (same deal, text and not a formula), and names in D2. To count how many times Smith worked on Mondays in January =SUMPRODUCT((A2:A1000="01")*(C2:C1000="Monday")*(D 2:D1000="Smith")) If your dates are really serial number dates, use =MONTH(B2) instead of =LEFT(B2,2) in your helper column and A2:A1000=1 instead of A2:A1000="01" as the first SUMPRODUCT argument. "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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple conditions for sumproduct
Mr Acquire, I used your suggestion as well to replace sumif with sumproduct
for multiple conditions and I must say it's a wonderful tool. thank you |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
multiple conditions for sumproduct
You're welcome. If you'd like to learn more about SUMPRODUCT, the following
site is pretty good: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "peter vh" wrote: Mr Acquire, I used your suggestion as well to replace sumif with sumproduct for multiple conditions and I must say it's a wonderful tool. thank you |
| Reply |
| 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 | |||
Linear Mode
