Sum when Multiple Criteria are met

[bbishop222]

I am trying to sum some items based on 3 different criteria. My data is laid
out as follows:
CustYear SlsYear M7 M8 M9 M10 Grand Total
2004 2005 $26,453 $31,274 $24,862 $19,622 $102,211
2006 $43,423 $55,347 $45,120 $48,772 $192,663
2007 $97,685 $60,947 $- $- $158,632
2005 2005 $24,995 $34,191 $31,900 $29,308 $120,395
2006 $53,176 $73,044 $53,849 $69,789 $249,859
2007 $111,734 $67,111 $- $- $178,844
2006 2005 $13 $7 $3 $5 $28
2006 $23,052 $38,514 $32,152 $41,222 $134,940
2007 $74,681 $46,680 $- $- $121,361
2007 2005 $111 $189 $14 $70 $384
2006 $214 $65 $62 $89 $430
2007 $31,574 $27,809 $- $- $59,383

What I want is a summary that looks like this:
2005 2006 2007
Base $116,914 $224,991 $337,477
2006 $19 $61,566 $121,361
2007 $301 $279 $59,383
$117,234 $286,836 $518,221
For rows:
1) I want everything with a CustYear prior to 2006 in my base Group. I want
everything with a CustYear of 2006 or 2007 in their respective buckets.
2) I only want year-to-date months (in this example through 8).

For columns:
1) I want the data related to the correct SlsYear in the correct column.

I've tried using sumproduct and I keep getting #Value!
=SUMPRODUCT(--(Sheet2!$A$3:$A$17<2006),--(Sheet2!$C$2:$N$2<9),--(Sheet2!$B$3:$B$17=2005),Sheet2!$C$3:$N$17)

Any help would be greatly appreciated!

Thank you,
Beth

 

[iliace]

I don't understand exactly what you're trying to do, but SUMPRODUCT
only takes ranges with the same dimensions. Try using the TRANSPOSE
function on the $C$2:$N$2 ranges, and also keep in mind that the
columns are 3 cells longer than the rows so you'll have to adjust your
ranges or come up with an alternate formula.

 

[bbishop222]

Thank you for the recommendation. Unfortunately I can't do that. My
underlying data is a pivot table (which is being fed from an SQL database).

I changed my dataset to have different pivots for each sales year, so now I
have the CustYear as row, and the Month as column. But I still haven't been
able to figure out how to get Excel to calculate the amounts I need.

Is it possible to use a nested SUMIF function when summing data in multiple
columns?

The information is being used for monthly summary sales reporting. I know I
could limit the months in my pivot table, however I don't want to have to
update which months are selected with each monthly refresh. Thus the reason
I'm trying to use formulas.

Thank you,
Beth

 

[iliace]

Sorry, I'm just not following all of the information in the
PivotTable. The date match (<2006) is on column A, so each blank will
return 0 (meaning true). I don't follow what the C2:N9 range
contains, and how < 9 will affect your result. And, I'm not entirely
sure of the purpose of the =2005 condition. What is CustYear and how
is it different from SlsYear? Perhaps I'm not recognizing the concept
behind your analysis.


If you can clarify what you need exactly, this might be easier.