Excel Beginner,
 

Hi Everyone,

Im hoping someone can help me, I am creating a workbook to record amounts
collected by supplier, and need to have a column for the state of origin,
delivery site, and product type for other reports and pivot tables to run off.

But i want to "sumif" based of two criteria, ie sum the cells if col D = on
site and, col E = a certain product type. can i do this without having to
enter in more columns. It seems like it should be something i am able to
do...is it possible?

Thanks

Excel Beginner,
 

You'll have to use SUMPRODUCT() to do that. For Example... use 2 seperate
cells to type in the data you want it to match. So type a certain product
type in one cell and on site in another cell. Then in the cell you want the
value to pop up type.

=SUMPRODUCT((D1:D50=CellVal1)*(E1:E50=CellVal2)*(R ange you want to sum))

This has to be enter by holding down CTRL + SHIFT + ENTER

FYI- Creating cell references like CellVal1 is a lot easier then typing in
the name. This way you can just change the cell or create a field of
different requirements and just copy and paste the formulas down. So
CellVal1 you would type in the cell that has the first requirement you want
to be met, so on with CellVal2.

"TSNS" wrote:

Hi Everyone,

Im hoping someone can help me, I am creating a workbook to record amounts
collected by supplier, and need to have a column for the state of origin,
delivery site, and product type for other reports and pivot tables to run off.

But i want to "sumif" based of two criteria, ie sum the cells if col D = on
site and, col E = a certain product type. can i do this without having to
enter in more columns. It seems like it should be something i am able to
do...is it possible?

Thanks

Excel Beginner,
 

You don't need "ctrl+shift+enter" with SUMPRODUCT, just hit "enter" will do
the job.


"AKphidelt" wrote:

You'll have to use SUMPRODUCT() to do that. For Example... use 2 seperate
cells to type in the data you want it to match. So type a certain product
type in one cell and on site in another cell. Then in the cell you want the
value to pop up type.

=SUMPRODUCT((D1:D50=CellVal1)*(E1:E50=CellVal2)*(R ange you want to sum))

This has to be enter by holding down CTRL + SHIFT + ENTER

FYI- Creating cell references like CellVal1 is a lot easier then typing in
the name. This way you can just change the cell or create a field of
different requirements and just copy and paste the formulas down. So
CellVal1 you would type in the cell that has the first requirement you want
to be met, so on with CellVal2.

"TSNS" wrote:

Hi Everyone,

Im hoping someone can help me, I am creating a workbook to record amounts
collected by supplier, and need to have a column for the state of origin,
delivery site, and product type for other reports and pivot tables to run off.

But i want to "sumif" based of two criteria, ie sum the cells if col D = on
site and, col E = a certain product type. can i do this without having to
enter in more columns. It seems like it should be something i am able to
do...is it possible?

Thanks

Excel Beginner,
 

Awesome!!! Thanks Guys!!! that works a treat!

"Teethless mama" wrote:

You don't need "ctrl+shift+enter" with SUMPRODUCT, just hit "enter" will do
the job.


"AKphidelt" wrote:

You'll have to use SUMPRODUCT() to do that. For Example... use 2 seperate
cells to type in the data you want it to match. So type a certain product
type in one cell and on site in another cell. Then in the cell you want the
value to pop up type.

=SUMPRODUCT((D1:D50=CellVal1)*(E1:E50=CellVal2)*(R ange you want to sum))

This has to be enter by holding down CTRL + SHIFT + ENTER

FYI- Creating cell references like CellVal1 is a lot easier then typing in
the name. This way you can just change the cell or create a field of
different requirements and just copy and paste the formulas down. So
CellVal1 you would type in the cell that has the first requirement you want
to be met, so on with CellVal2.

"TSNS" wrote:

Hi Everyone,

Im hoping someone can help me, I am creating a workbook to record amounts
collected by supplier, and need to have a column for the state of origin,
delivery site, and product type for other reports and pivot tables to run off.

But i want to "sumif" based of two criteria, ie sum the cells if col D = on
site and, col E = a certain product type. can i do this without having to
enter in more columns. It seems like it should be something i am able to
do...is it possible?

Thanks