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