SUMIF with 2 conditions

=SUMPRODUCT(--(A2:A10="Yvonne"),--(B2:B10=4),C2:C10)

or

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10)


where D2 would hold the names and E2 the week numbers

--
Regards,

Peo Sjoblom

(No private emails please)


"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Try...

=SUMPRODUCT(--($A$2:$A$5="Yvonne"),--($B$2:$B$5=4),$C$2:$C$5)

or

=SUMPRODUCT(--($A$2:$A$5=E2),--($B$2:$B$5=F2),$C$2:$C$5)

....where E2 contains the name of interest, and F2 contains the week
number of interest.

Hope this helps!

In article <[email protected]>,
"Simon" <[email protected]> wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Hi!

Try this:

=SUMPRODUCT(--(A1:A10="persons_name"),--(B1:B10=4),C1:C10)

Biff

"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Alternately you could use

=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10)

Joining conditions with "*" does the same thing as prefixing each
condition with "--"; it converts the boolean condition array to 0's and
1's. Which form is preferable is largely a matter of taste. I prefer
this form because
- it reduces the formula length by 2 characters per condition
- IMHO it is easier for a new user to understand (some will disagree)
- It generalizes to more complicated combinations of conditions. Use
"+" instead of "*" to join the conditions with OR instead of AND. If
you combine more than two conditions, some with "*" and some with "+",
you can use parentheses to control the order of evaluation.

Jerry

Simon wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Alternately you could use

=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10)

Joining conditions with "*" does the same thing as prefixing each
condition with "--"; it converts the boolean condition array to 0's and
1's. Which form is preferable is largely a matter of taste. I prefer
this form because
- it reduces the formula length by 2 characters per condition
- IMHO it is easier for a new user to understand (some will disagree)
- It generalizes to more complicated combinations of conditions. Use
"+" instead of "*" to join the conditions with OR instead of AND. If
you combine more than two conditions, some with "*" and some with "+",
you can use parentheses to control the order of evaluation.

Jerry

Simon wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Alternately you could use

=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10)

Joining conditions with "*" does the same thing as prefixing each
condition with "--"; it converts the boolean condition array to 0's and
1's. Which form is preferable is largely a matter of taste. I prefer
this form because
- it reduces the formula length by 2 characters per condition
- IMHO it is easier for a new user to understand (some will disagree)
- It generalizes to more complicated combinations of conditions. Use
"+" instead of "*" to join the conditions with OR instead of AND. If
you combine more than two conditions, some with "*" and some with "+",
you can use parentheses to control the order of evaluation.

Jerry

Simon wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

=SUMPRODUCT(--(A2:A10="Yvonne"),--(B2:B10=4),C2:C10)

or

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10)


where D2 would hold the names and E2 the week numbers

--
Regards,

Peo Sjoblom

(No private emails please)


"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Try...

=SUMPRODUCT(--($A$2:$A$5="Yvonne"),--($B$2:$B$5=4),$C$2:$C$5)

or

=SUMPRODUCT(--($A$2:$A$5=E2),--($B$2:$B$5=F2),$C$2:$C$5)

....where E2 contains the name of interest, and F2 contains the week
number of interest.

Hope this helps!

In article <[email protected]>,
"Simon" <[email protected]> wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Hi!

Try this:

=SUMPRODUCT(--(A1:A10="persons_name"),--(B1:B10=4),C1:C10)

Biff

"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Alternately you could use

=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10)

Joining conditions with "*" does the same thing as prefixing each
condition with "--"; it converts the boolean condition array to 0's and
1's. Which form is preferable is largely a matter of taste. I prefer
this form because
- it reduces the formula length by 2 characters per condition
- IMHO it is easier for a new user to understand (some will disagree)
- It generalizes to more complicated combinations of conditions. Use
"+" instead of "*" to join the conditions with OR instead of AND. If
you combine more than two conditions, some with "*" and some with "+",
you can use parentheses to control the order of evaluation.

Jerry

Simon wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Hi I need to do a function, I have the following columns and some sample data

Name Week Number. Estimated Hours
Yvonne 4 2
Yvonne 4 3
Simon 4 5
Yvonne 5 1

What i want to do is, for each person i want to some their total estimated
hours for week 4.
So in pseudocode im trying to do this:
SUM the estimated hours where the name = Yvonne and the week number = 4.
The answer should be 5 for this example

Can someone please help me to do this, im struggling.

Thanks.
Simon

=SUMPRODUCT(--(A2:A10="Yvonne"),--(B2:B10=4),C2:C10)

or

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10)


where D2 would hold the names and E2 the week numbers

--
Regards,

Peo Sjoblom

(No private emails please)


"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Try...

=SUMPRODUCT(--($A$2:$A$5="Yvonne"),--($B$2:$B$5=4),$C$2:$C$5)

or

=SUMPRODUCT(--($A$2:$A$5=E2),--($B$2:$B$5=F2),$C$2:$C$5)

....where E2 contains the name of interest, and F2 contains the week
number of interest.

Hope this helps!

In article <[email protected]>,
"Simon" <[email protected]> wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon

Hi!

Try this:

=SUMPRODUCT(--(A1:A10="persons_name"),--(B1:B10=4),C1:C10)

Biff

"Simon" <[email protected]> wrote in message
news:[email protected]...
> Hi I need to do a function, I have the following columns and some sample
> data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon
>
>

Alternately you could use

=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10)

Joining conditions with "*" does the same thing as prefixing each
condition with "--"; it converts the boolean condition array to 0's and
1's. Which form is preferable is largely a matter of taste. I prefer
this form because
- it reduces the formula length by 2 characters per condition
- IMHO it is easier for a new user to understand (some will disagree)
- It generalizes to more complicated combinations of conditions. Use
"+" instead of "*" to join the conditions with OR instead of AND. If
you combine more than two conditions, some with "*" and some with "+",
you can use parentheses to control the order of evaluation.

Jerry

Simon wrote:

> Hi I need to do a function, I have the following columns and some sample data
>
> Name Week Number. Estimated Hours
> Yvonne 4 2
> Yvonne 4 3
> Simon 4 5
> Yvonne 5 1
>
> What i want to do is, for each person i want to some their total estimated
> hours for week 4.
> So in pseudocode im trying to do this:
> SUM the estimated hours where the name = Yvonne and the week number = 4.
> The answer should be 5 for this example
>
> Can someone please help me to do this, im struggling.
>
> Thanks.
> Simon