Complex conditions using SUMIF
 

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

Hi,

Try this

=SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5))

or if you want to include the employee

=SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

SUMPRODUCT is the key to using multiple conditions.

=SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10)

Note that you can add another criteria to check by Employee, if you wanted:
=SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10)

Just make sure that all the array sizes withing SUMPRODUCT are the same size.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

Thanks so much...................can I use NOT EQUAL TO? Ex:

=SUMPRODUCT((B1:B5<"A")*(C1:C50)*(C1:C5))

I actually had more than 2 statuses.....I wanted a sum for all T's; then a
sum for everything else.

The < is only returning a value of zero.......which I don't think is right.

--
~~Boo


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5))

or if you want to include the employee

=SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

I'm a goofball...........had a syntax error..........it worked
PERFECTLY.......thnx a bunch.
--
~~Boo


"Booweezie" wrote:

Thanks so much...................can I use NOT EQUAL TO? Ex:

=SUMPRODUCT((B1:B5<"A")*(C1:C50)*(C1:C5))

I actually had more than 2 statuses.....I wanted a sum for all T's; then a
sum for everything else.

The < is only returning a value of zero.......which I don't think is right.

--
~~Boo


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5))

or if you want to include the employee

=SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

is there a COUNTPRODUCT function? I now find that I need to count the number
of occurrences that were summed in my example.
--
~~Boo


"Luke M" wrote:

SUMPRODUCT is the key to using multiple conditions.

=SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10)

Note that you can add another criteria to check by Employee, if you wanted:
=SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10)

Just make sure that all the array sizes withing SUMPRODUCT are the same size.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

is there a COUNTPRODUCT function? I now find that I need to count the
number
of occurrences that were summed in my example.


=SUMPRODUCT((B1:B5="A")*(C1:C50))

should do that


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Booweezie" wrote:

is there a COUNTPRODUCT function? I now find that I need to count the number
of occurrences that were summed in my example.
--
~~Boo


"Luke M" wrote:

SUMPRODUCT is the key to using multiple conditions.

=SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10)

Note that you can add another criteria to check by Employee, if you wanted:
=SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10)

Just make sure that all the array sizes withing SUMPRODUCT are the same size.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Booweezie" wrote:

Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.

--
~~Boo

Complex conditions using SUMIF
 

Booweezie wrote:
is there a COUNTPRODUCT function? I now find that I need to count the number
of occurrences that were summed in my example.

Look he

http://www.contextures.com/xlFunctio...tml#SumProduct

Complex conditions using SUMIF
 

Complex conditions using SUMIF

As a side note, Sumproduct is the most popular, but Excel 2007 has the
newer "SumIfs" function.
I prefer to use the heading row for Named Ranges.
For Example...

=SUMIFS(Amount, Status, "A", Amount, "0")

or

=SUMIFS(Amount, Status, "<A", Amount, "0")


= = = = = = =
HTH :)
Dana DeLouis

On 2/15/2010 12:26 PM, Booweezie wrote:
Hi Smart Folk:

I need to analyze employee date and am having problems. Here's my data:

Employee Number Status Amount
--------------------- ------- ---------
Emp #1 A $3.00
Emp #1 T ($2.25)
Emp #1 T $14.00
Emp #1 A ($7.50)
Emp #1 A $1.30

I need to know the sum of Amount by employment status ONLY if the amount is
greater than zero. The answers for this table that I want to get a

A = $4.30
T = $14.00

I can use the SUMIF function to get a total for A's and T's; but I'm lost on
how I can limit it only to amounts greater than zero.

Thanks for any help you can provide.