sumif criteria

[Rick Stahl]

The following expression works fine in Excel 2002 ver SP3:

=SUMIF(Carlos!B14:B74,"<6",Carlos!F14:F74)

When values in B are less than 6 the corresponding values in F are summed.
However, I do not know how to write in the correct format when I wish to sum
values in F for the B values of 6, 7, 8, and 9.

Thanks in advance for any help.

 

[Tyro]

You could use:
=SUMPRODUCT(--(Carlos!B14:B74>=6),--(Carlos!B14:B74<=9),Carlos!F14:F74)

Tyro

 

[Rick Stahl]

WOW ! Great ! Thank you. I will give it a try.

If you don't mind me asking, where could I learn the formats for these types
of expressions ?

Thanks again !

 

[Tyro]

Among others

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

Do a google for Excel sumproduct.

 

[T. Valko]

Another one:

=SUM(SUMIF(Carlos!B14:B74,{6,7,8,9},Carlos!F14:F74))

 

[Bob Phillips]

A simpler way is

=SUMIF(Carlos!B14:B74,">=6",Carlos!F14:F74)-SUMIF(Carlos!B14:B74,">9",Carlos!F14:F74)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Stahl]

Thank you both for your help. I got it working ! I also appreciate the web
site.