Thread: multiple if criteria in sumif

1. Excel 2003
I have a workbook, where I want to sum data from one tab into another, based on 2 sets of criteria.

The first tab (where I want everything to sum up at) Has a week ending (w/e) date column(ex: 07/03/09, say in cell B1). & It has variance categories (ex. Ahead of Schedule (say A2).
The second tab also has a w/e date column (A:A) & the variance categories (B:, & hour values assigned (C:C).

I want the first tab to sum everything from the second tab as long as the dates & categories match....

Like a sumif : Sheet1!B1=sheet2!A:A, and Sheet2!B:B="Ahead of Schedule"...If those two criterias are met, I want it to return the sum of hours in sheet2!C:C. Sheet2, column B may have several entries for w/e 7/3/09...with various variance categories. It also may contain other w/e dates, like 6/26/09 or 6/19/09. However, each week, I am only concerned with the previous w/e date...so each week Sheet1!A2 's date will change & I'll need the sum to only show for the new w/e date. That's why I need it referenced to the cell, not an actual date value from the first tab.

any and all help would be Greatly appreciated!!! Thank you!

2. I'm not sure I completely understand your description, but you should be able to use a formula similar to this in a cell on Sheet1:

=SUMPRODUCT((Sheet2!\$A\$2:\$A\$1000=B1)*(Sheet2!\$B\$2: \$B\$1000=A2)*Sheet2!\$C\$2:\$C\$1000)

where B1 is the cell on Sheet1 with the reference date and A2 is the cell on Sheet1 with the variance category.

Adjust the bounds 2 ... 1000 as needed.

3. Here is an example of the file I'm working with....
I'm concentrating to correct the formula on "Wed-Presentation" tab, Row 5 to only calculate the items from "AA3 0801" tab that match the category & w/e date from "Wed-Presentation" tab.

Row 6 is calculating the same data, just not distinguishing a week ending date.

--I'm not sure if the formula you provided will do what I need.

Again, thank you very much!!!!

4. You can use this formula in D5 on Wed-Presentation:

=SUMPRODUCT((INDIRECT("'"&\$B5&" "&\$C5&"'!A2:A8")=\$A5)*(INDIRECT("'"&\$B5&" "&\$C5&"'!F2:F8")=D\$1)*INDIRECT("'"&\$B5&" "&\$C5&"'!E2:E8"))

This formula can be filled right to U5, and it can be copied to D8:U8, D11:U11 etc. I've assumed that you'll enter a date in A8, A11 etc. If not, change \$A5 to \$A\$5 in the formula, all rows will then refer back to A5.

5. [quote name='HansV' post='783634' date='08-Jul-2009 21:07']You can use this formula in D5 on Wed-Presentation:

=SUMPRODUCT((INDIRECT("'"&\$B5&" "&\$C5&"'!A2:A8")=\$A5)*(INDIRECT("'"&\$B5&" "&\$C5&"'!F2:F8")=D\$1)*INDIRECT("'"&\$B5&" "&\$C5&"'!E2:E8"))

This formula can be filled right to U5, and it can be copied to D8:U8, D11:U11 etc. I've assumed that you'll enter a date in A8, A11 etc. If not, change \$A5 to \$A\$5 in the formula, all rows will then refer back to A5.[/quote]

Yes that worked! Thank you so much Hans!!!! I would have thought I would have indicated the date value & category values & all! Thank you so much!!!!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•