ExcelBanter - calculating SUM with two conditions

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - calculating SUM with two conditions (https://www.excelbanter.com/excel-worksheet-functions/19165-calculating-sum-two-conditions.html)

calculating SUM with two conditions

I have a main spreadsheet with columns as follow:
A C AT
Product ID Group TOTAL
BBB TOP formula ?
SSS TOP
CCC AAP
BBB AAP

And some spreadsheets with the same structure
Sheet 1:
A C AT
Product ID Group TOTAL
BBB TOP 100
SSS AAP 200
CCC AAP 100
CCC TOP 400

The same product can be for different groups.

To calculate just TOTAL for Product ID without Group Im using the following
formula:
=SUMIF('Sheet 1'!$A$4:$A$390,A3, 'Sheet 1'!$AY$4:$AY$390) for the
spreadsheet where are only Product ID and TOTAL

This formula is taking the same Product ID from the Sheet1 and calculating
the SUM.
Its working well.

How is it possible to take the same product and the same group from the
Sheet 1 to calculate SUM?

Thanks

You need to create an array formula. Check out Chip Pearson's site.
http://www.cpearson.com/excel/array.htm
HTH,
Gary Brown

"Alex" wrote:

I have a main spreadsheet with columns as follow:
A C AT
Product ID Group TOTAL
BBB TOP formula ?
SSS TOP
CCC AAP
BBB AAP

And some spreadsheets with the same structure
Sheet 1:
A C AT
Product ID Group TOTAL
BBB TOP 100
SSS AAP 200
CCC AAP 100
CCC TOP 400

The same product can be for different groups.

To calculate just TOTAL for Product ID without Group Im using the following
formula:
=SUMIF('Sheet 1'!$A$4:$A$390,A3, 'Sheet 1'!$AY$4:$AY$390) for the
spreadsheet where are only Product ID and TOTAL

This formula is taking the same Product ID from the Sheet1 and calculating
the SUM.
Its working well.

How is it possible to take the same product and the same group from the
Sheet 1 to calculate SUM?

Thanks