What function should I be using for...

[Jambruins]

Attached is my spreadsheet. I am trying to come up with a functio
column G that will add all the outcomes (column C) that are within th
100-109 range and also have a W in the result (B) column. I also wan
the function to subtract the outcomes in the 100-109 range with a L i
the result column. How do I do this? Thank

Attachment filename: help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61227

 

[Jambruins]

I think I should be using the sumif command with and and or function
also. I cannot get it to work though. Am I approaching it the righ
way or is there a better way to do it? Thank

 

[RagDyer]

I didn't open your file, but from your description, this should work with
just a change in references:

=SUMPRODUCT((B1:B100="W")*(C1:C100>=100)*(C1:C100<=109)*C1:C100)-SUMPRODUCT(
(B1:B100="L")*(C1:C100>=100)*(C1:C100<=109)*C1:C100)


HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Attached is my spreadsheet. I am trying to come up with a function
column G that will add all the outcomes (column C) that are within the
100-109 range and also have a W in the result (B) column. I also want
the function to subtract the outcomes in the 100-109 range with a L in
the result column. How do I do this? Thanks

Attachment filename: help.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=612274

 

[Myrna Larson]

The following should give you the total of column C where C is between 100 and
109 and B = W

=SUMPRODUCT(--(C1:C100>=100),--(C1:C100<=109),--(B1:B100="W"),C1:C100)-
SUMPRODUCT(--(C1:C100>=100),--(C1:C100<=109),--(B1:B100="L"),C1:C100)

The corresponding array formula (enter it with CTRL+SHIFT+ENTER)

=SUM((C1:C100>=100)*(C1:C100<=109)*(B1:B100="W")*(C1:C100))-
SUM((C1:C100>=100)*(C1:C100<=109)*(B1:B100="L")*(C1:C100))

Change the 1's and 100's to the appropriate row numbers.

You can try a pivot table, where you group the values in the C column: use
column C as a row field and group appropriately, column B as a column field,
and column C as the data field using the SUM function. That will give you all
the possible results, I think.