Sumif using multiple criteria...

[redrazorneck]

I need to do a "sumif" based on multiple criteria. For example, I'd like to sum values in column D as long as column A AND column B are meeting a certain (but separate) value. Thanks in advance for any help...

[Aladin Akyurek]

On 2002-05-22 14:40, redrazorneck wrote:
I need to do a "sumif" based on multiple criteria. For example, I'd like to sum values in column D as long as column A AND column B are meeting a certain (but separate) value. Thanks in advance for any help...

=SUMPRODUCT((rangeA=critA)*(rangeB=critB),rangeD)

[Mark W.]

On 2002-05-22 14:40, redrazorneck wrote:
I need to do a "sumif" based on multiple criteria. For example, I'd like to sum values in column D as long as column A AND column B are meeting a certain (but separate) value. Thanks in advance for any help...

{=SUM((A2:A10=value1)*(B2:B10=value2)*D2:D10)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

[redrazorneck]

Thanks, guys. Will try it tomorrow @ work...

[Juan Pablo González]

Oops, I read "countif", not "sumif"... change that formula to

=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2)*Range3)

where Range3 is the range you want to SUM.

[redrazorneck]

I'm getting a return value of "#N/A" on both of these formulas. Let me explain in more detail what I'm trying to do. I have one worksheet with my production data. Included in this sheet are item numbers (Column A), production quantities (Column B), and dates (Column C) in which that quantity is due to be completed. On another sheet, I'm summarizing this data into "two-week" buckets for each item number. The column headings on this sheet is the last date of the two-week schedule, and the first column simply has each item number listed. I'm trying to sum the production quantities in the summary sheet so that two criterion are met: the item number in column A matches the item number in my detail; and, the date in which this item is due must be less than or equal to the date in the column heading. Again, I'm getting the #N/A error whenever I try your suggestions. Any ideas as to what the problem is (probably somewhere between the keyboard and the brain)? Thanks again for your help....

[Asala42]

Hey,

Do any of your ranges contain a #N/A error? (say from a Vlookup for example). That would cause a sumproduct for example to kick out the same error.

If this is the case, you'll probably want to remove these entries, either manually or by if-statement if possible.

Hope that helps,
Adam

[Aladin Akyurek]

On 2002-05-23 06:39, redrazorneck wrote:
I'm getting a return value of "#N/A" on both of these formulas. Let me explain in more detail what I'm trying to do. I have one worksheet with my production data. Included in this sheet are item numbers (Column A), production quantities (Column B), and dates (Column C) in which that quantity is due to be completed. On another sheet, I'm summarizing this data into "two-week" buckets for each item number. The column headings on this sheet is the last date of the two-week schedule, and the first column simply has each item number listed. I'm trying to sum the production quantities in the summary sheet so that two criterion are met: the item number in column A matches the item number in my detail; and, the date in which this item is due must be less than or equal to the date in the column heading. Again, I'm getting the #N/A error whenever I try your suggestions. Any ideas as to what the problem is (probably somewhere between the keyboard and the brain)? Thanks again for your help....

Would you post the formula as you applied it?

[redrazorneck]

Outstanding! I think I got it to work, but I had to use the array. Here's my formula:

{=SUM(('Pegging Inquiry'!$J$2:$J$460='Firm Schedule'!$A3)*('Pegging Inquiry'!$A$2:$A$460<'Firm Schedule'!B$2)*'Pegging Inquiry'!$B$2:$B$460)}

where Pegging Inquiry is my data sheet, column J in Pegging Inquiry is my item number, and I'm matching it up with an item number in my summary sheet (Firm Schedule). Also, my dates are in column A of Pegging Inquiry and in row 2 of Firm Schedule.

I couldn't get the SUMPRODUCT to work, but I'm not too worried about it anymore! Thanks to all that helped with this problem. I am now sold on this nugget of a website!

[Aladin Akyurek]

On 2002-05-23 08:17, redrazorneck wrote:
Outstanding! I think I got it to work, but I had to use the array. Here's my formula:

{=SUM(('Pegging Inquiry'!$J$2:$J$460='Firm Schedule'!$A3)*('Pegging Inquiry'!$A$2:$A$460<'Firm Schedule'!B$2)*'Pegging Inquiry'!$B$2:$B$460)}

where Pegging Inquiry is my data sheet, column J in Pegging Inquiry is my item number, and I'm matching it up with an item number in my summary sheet (Firm Schedule). Also, my dates are in column A of Pegging Inquiry and in row 2 of Firm Schedule.

I couldn't get the SUMPRODUCT to work, but I'm not too worried about it anymore! Thanks to all that helped with this problem. I am now sold on this nugget of a website!

If this array-formula with boolean terms

{=SUM(('Pegging Inquiry'!$J$2:$J$460='Firm Schedule'!$A3)*('Pegging Inquiry'!$A$2:$A$460<'Firm Schedule'!B$2)*'Pegging Inquiry'!$B$2:$B$460)}

work,

=SUMPRODUCT(('Pegging Inquiry'!$J$2:$J$460='Firm Schedule'!$A3)*('Pegging Inquiry'!$A$2:$A$460<'Firm Schedule'!B$2)*'Pegging Inquiry'!$B$2:$B$460)

will also work. They are exactly equivalent.