Add qualifier to andif statement
 

I have a spreadsheet formua that I'd like to add a test to, for name in
another column.
The formula is =SUMIF($F$3:$F$218,"="&A3,$G$3:$G$218)
-SUMIF($F$3:$F$218,""&B2,$G$3:$G$218)

In this case I want to test a name in column H and only count G if name is
equal

So far I know a bunch of ways....that won't work..LOL....can someone help me
out

thanks.
Steve

Add qualifier to andif statement
 

"litngldy" wrote:

I have a spreadsheet formua that I'd like to add a test to, for name in
another column.
The formula is =SUMIF($F$3:$F$218,"="&A3,$G$3:$G$218)
-SUMIF($F$3:$F$218,""&B2,$G$3:$G$218)

In this case I want to test a name in column H and only count G if name is
equal

So far I know a bunch of ways....that won't work..LOL....can someone help me
out

thanks.
Steve


CRUD.....I know I know....it's SUMIF not ANDIF....sorry

Add qualifier to andif statement
 

You need SUMPRODUCT; see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
To add column G subject to test of F and H
=SUMPRODUCT(--($F$3:$F$100=$A$3),--($H$3:$H$100="January"), $G$3:$G$100)
Quotes needed in test only for text not for operators.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"litngldy" wrote in message
...
I have a spreadsheet formua that I'd like to add a test to, for name in
another column.
The formula is =SUMIF($F$3:$F$218,"="&A3,$G$3:$G$218)
-SUMIF($F$3:$F$218,""&B2,$G$3:$G$218)

In this case I want to test a name in column H and only count G if name
is
equal

So far I know a bunch of ways....that won't work..LOL....can someone help
me
out

thanks.
Steve

Add qualifier to andif statement
 

Bernard,
I'm confused...I'm summing data in F that meets criteria A3 to B3, now I
want to
only sum if H equals "name"

A3= Jan1
B3=Jan31
F$3:F$218 are entry dates
G$3:G$218 are hours worked
H$3:H$218 are employee name

I want to sum any Jan1-Jan31 hours for "Bob"

I hope that's clearer

Thanks alot for the help

Steve

"Bernard Liengme" wrote:

You need SUMPRODUCT; see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
To add column G subject to test of F and H
=SUMPRODUCT(--($F$3:$F$100=$A$3),--($H$3:$H$100="January"), $G$3:$G$100)
Quotes needed in test only for text not for operators.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"litngldy" wrote in message
...
I have a spreadsheet formua that I'd like to add a test to, for name in
another column.
The formula is =SUMIF($F$3:$F$218,"="&A3,$G$3:$G$218)
-SUMIF($F$3:$F$218,""&B2,$G$3:$G$218)

In this case I want to test a name in column H and only count G if name
is
equal

So far I know a bunch of ways....that won't work..LOL....can someone help
me
out

thanks.
Steve

Add qualifier to andif statement
 

This SUMS
=SUMPRODUCT(--($F$3:$F$100=$A$3),--($F$3:$F$100<=$B$3),--($H$3:$H$100="name"),
$f$3:$f$100)
This counts
=SUMPRODUCT(--($F$3:$F$100=$A$3),--($F$3:$F$100<=$B$3),--($H$3:$H$100="name"))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"litngldy" wrote in message
...
Bernard,
I'm confused...I'm summing data in F that meets criteria A3 to B3, now I
want to
only sum if H equals "name"

A3= Jan1
B3=Jan31
F$3:F$218 are entry dates
G$3:G$218 are hours worked
H$3:H$218 are employee name

I want to sum any Jan1-Jan31 hours for "Bob"

I hope that's clearer

Thanks alot for the help

Steve

"Bernard Liengme" wrote:

You need SUMPRODUCT; see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
To add column G subject to test of F and H
=SUMPRODUCT(--($F$3:$F$100=$A$3),--($H$3:$H$100="January"), $G$3:$G$100)
Quotes needed in test only for text not for operators.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"litngldy" wrote in message
...
I have a spreadsheet formua that I'd like to add a test to, for name in
another column.
The formula is =SUMIF($F$3:$F$218,"="&A3,$G$3:$G$218)
-SUMIF($F$3:$F$218,""&B2,$G$3:$G$218)

In this case I want to test a name in column H and only count G if
name
is
equal

So far I know a bunch of ways....that won't work..LOL....can someone
help
me
out

thanks.
Steve