Sum If using 2 criterias
 

Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" = Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" = Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

Try this:

=SUMPRODUCT(--(month_range=C3),--(account_range=B6),amount_range)

Biff

"Michael" wrote in message
...
Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Pivot table, elegant?

LOL! <vbg

Biff

"Dave F" wrote in message
...
You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the
other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

Dave,

How do I get the amount using this formula?
By the way thanks for your quick response!

"Dave F" wrote:

You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" = Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

Dr. Valko!!

Worked Like a charm!! Just one question so that I can learn from this, what
does "--" do for the formula??

Thanks!!!!!

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(month_range=C3),--(account_range=B6),amount_range)

Biff

"Michael" wrote in message
...
Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

=SUMPRODUCT(--(month_range=C3),--(account_range=B6),amount_range)

Each element in these arrays will evaluate to either TRUE or FALSE:

(month_range=C3)
(account_range=B6)

The "--" coerces these logical values (TRUE, FALSE) to numbers. 1 for TRUE
and 0 for FALSE.

Then all 3 arrays are multiplied together and totaled for the final result.

See this for an extensive discussion:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Biff

"Michael" wrote in message
...
Dr. Valko!!

Worked Like a charm!! Just one question so that I can learn from this,
what
does "--" do for the formula??

Thanks!!!!!

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(month_range=C3),--(account_range=B6),amount_range)

Biff

"Michael" wrote in message
...
Hi,

I have 2 worksheets, one sheet with the columns listed below and the
other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

See http://xldynamic.com/source/xld.SUMPRODUCT.html

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Michael" wrote in message
...
Dr. Valko!!

Worked Like a charm!! Just one question so that I can learn from this,
what
does "--" do for the formula??

Thanks!!!!!

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(month_range=C3),--(account_range=B6),amount_range)

Biff

"Michael" wrote in message
...
Hi,

I have 2 worksheets, one sheet with the columns listed below and the
other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

Now come on Dr Valko, we need to keep our prejudice under wraps <G

Bob


"T. Valko" wrote in message
...
However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Pivot table, elegant?

LOL! <vbg

Biff

"Dave F" wrote in message
...
You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the
other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

See T. Valko's response. He includes the amount whereas I didn't in my
response.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Dave,

How do I get the amount using this formula?
By the way thanks for your quick response!

"Dave F" wrote:

You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" = Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.

Sum If using 2 criterias
 

<G

Biff

"Bob Phillips" wrote in message
...
Now come on Dr Valko, we need to keep our prejudice under wraps <G

Bob


"T. Valko" wrote in message
...
However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Pivot table, elegant?

LOL! <vbg

Biff

"Dave F" wrote in message
...
You need to use SUMPRODUCT:

=SUMPRODUCT(--(A1:A10="Month"),--(B1:B10="Account"))

However, you may find that a pivot table is a more elegant solution for
your
data analysis needs.

Dave
--
Brevity is the soul of wit.


"Michael" wrote:

Hi,

I have 2 worksheets, one sheet with the columns listed below and the
other
sheet summarizes the activity entered in the first tab.

Column A = "Amount"
Column B = "Account"
Column C = "Month"

What I am trying to do is: SUMIF("Month"=Cell(C3) and "Account" =
Cell(B6))
then return the "Amount" based on the criteria.

How would I create a formula to do just that?

Please let me know if need to further explain.