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
|