Here's the problem I have:
I use a worksheet containing a back sheet with (more or less) raw data, and multiple front sheets I use for reporting, containing tables with data and some charts.
Let's say the datasheet contains four fields:
1) Customer name/number (column A);
2) Amount Due ($) (B);
3) Week number (C);
4) Year (D).
Now, I want to show the last thirteen weeks on the front sheets, with data for one customer per sheet, showing the Amount Due for that customer over the last thirteen weeks.
Selecting one customer is easy; however, I also have total sheets which should show the sum of the Amount Due of a selected number of customers. These customers are not 'fixed' i.e.: an end user should be able to input his/her selection of customer numbers (say: on the front sheet in column Z). The selection could contain 2, 3, 4 or more customers; nor the number of customers nor the customer numbers themselves are known in advance.
In other words: I would like to be able to sum the Amount Due where:
1) the customer number is either e.g. 1, 2, or 3 (= multiple criteria for field Customer Number);
2) the week number is e.g. 44 (= one criterion for field Week number);
3) the year is e.g. 2008 (= one criterion for field Year).
Suppose I have my selection of customer numbers stored in a defined range in column Z called 'selected customer numbers'.
1) The formula SUMIF doesn't work, since I can only enter one criterion.
2) The array formula SUM(IF($A$2:$A$1000=selected customer numbers;IF($C$2:$C$1000=44;IF($D$2:$D$1000=2008;$D$2:$D$1000;0)))) doesn't do the trick either: it doesn't accept the multiple criteria under 'selected customer numbers'.
3) I came across an array formula that does accept multiple criteria for one field: SUMPRODUCT(SUMIF($A$2:$A$1000;selected customer numbers;$B$2:$B$1000)). However: this formula only accepts criteria for one and the same field, so I can't limit my search results to the desired week(s) and year(s).
Any ideas how I could 'merge' formulas 2 and 3 so I can solve my problem? Many thanks in advance!
Bookmarks