Add qualifier to andif statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

litngldy - 08 Nov 2006 19:06 GMT

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

Reply to this Message

litngldy - 08 Nov 2006 19:24 GMT

> I have a spreadsheet formua that I'd like to add a test to, for name in
> another column.
[quoted text clipped - 9 lines]
> thanks.
> Steve

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

Reply to this Message

Bernard Liengme - 08 Nov 2006 20:00 GMT

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

Signature

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

>I have a spreadsheet formua that I'd like to add a test to, for name in
> another column.
[quoted text clipped - 11 lines]
> thanks.
> Steve

Reply to this Message

litngldy - 08 Nov 2006 20:33 GMT

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

> You need SUMPRODUCT; see
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
[quoted text clipped - 18 lines]
> > thanks.
> > Steve

Reply to this Message

Bernard Liengme - 08 Nov 2006 22:20 GMT

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"))

Signature

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

> Bernard,
> I'm confused...I'm summing data in F that meets criteria A3 to B3, now I
[quoted text clipped - 39 lines]
>> > thanks.
>> > Steve

Reply to this Message

Rate this thread: