SUMIF Multiple Criteria in different ranges.

[DonFlak]

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

 

[Don Guillett]

=sumproduct((MONDAY!$A$2:$A$50000=A$2)*(MONDAY!$D$2:$D$50000>7)*rng to sum)
suggest you limit your range to a more meaningful number or use a defined
name range that is self adjusting.

 

[DonFlak]

=sumproduct((MONDAY!$A$2:$A$50000=A$2)*(MONDAY!$D$2:$D$50000>7)*rng tosum)
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











- 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$50000>7)*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

 

[John]

Don, I have the same questioin. Did you get your answer?

 

[Laura Cook]

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

=sumproduct((MONDAY!$A$2:$A$50000=A$2)*(MONDAY!$D$2:$D$50000>7)*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











- 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$50000>7)*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

 

[MrAcquire]

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).

 

[MrAcquire]

Sorry, there was a typo.

=SUMPRODUCT((A2=MONDAY!$A$2:$A$50000)*(MONDAY!$D$2:$D$50000>7)*($D$2:$D$50000))

 

[MrAcquire]

Sorry, there was a typo.

=SUMPRODUCT((A2=MONDAY!$A$2:$A$50000)*(MONDAY!$D$2:$D$50000>7)*($D$2:$D$50000))

 

[John]

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?

 

[John]

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]

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")*(D21000="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.

 

[MrAcquire]

Or better yet, forget the helper column,
=SUMPRODUCT((LEFT(B2:B100),3)="01")*(C2:C100="Mon")*(D2100="Smith"))

or, if date is a serial number,
=SUMPRODUCT((MONTH(B2:B100)=1)*(C2:C100="Mon")*(D2100="Smith"))

 

[peter vh]

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

 

[MrAcquire]

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