questions about how to conditionally calculate the sum

**travis0909** · 03-01-2012, 06:32 PM

Hi everyone,

I don't know how to put it in words, the probelem is i want to calculate the total amount for each store and for each transaction code on a mothly basis . Every month the sheet is different. But the format is the same. For example: the total of store 1 and code 1 is $1+$2=$3. but next month the number maybe different, maybe there will be more transaction 1 occur in store 1. So all i want is not a answer just for this example but a logic expression which can fit any change in this sheet, holding the basic format constant. Thank you!

**JieJenn** · 03-01-2012, 06:37 PM

Pivot Table is your friend on this one

**darkyam** · 03-01-2012, 06:40 PM

Another way is to set up a table on sheet 2, with Store in A, Transaction Code in B, Total in C. C2 and down has this formula: =SUMPRODUCT((Sheet1!$B$2:$B$21=A2)*(Sheet1!$D$2:$D$21=B2)*(Sheet1!$C$2:$C$21))