Sumif

[travelersway]

--------------------------------------------------------------------------------

=SUMIF(O25:O55,"= P26",J25:J55) should equal 3 in cell Q26

I'm trying to sum the values in Column J, for the value in column P,
which may be located in column O once or multiple times


How do I write this formula to use the value of cell P26? The present
formula results in a blank cell?

If I substitute the value (70) for P26 the results will be correct as
there is only one 70 in column O25:O55 and one value of 3 in column
J25:J55.

If I substitute the value for P27 (80) in the formula, there are two
80's in column O25:O55 and two values ( one of 2 and one of 3 ) in
column J25:J55, the results is a fictious number like 3.5.

Any help is appreciated.
Travelersway

 

[swatsp0p]

This is not documented in the HELP files...

=SUMIF(O25:O55,"= P26",J25:J55) should be:

=SUMIF(O25:O55,"="&P26,J25:J55)

Does it work now?

 

[olasa]

Generally, this how to concatenate the two:
=SUMIF(O25:O55,"="&P26,J25:J55)

But in this case, the below will be enough:
=SUMIF(O25:O55,P26,J25:J55)

HTH
Ola Sandströ

 

[travelersway]

Thank you both !

I edited the thread to indiate that your kindness resolved parts A & B
but part C is still a problem.

Again, your help is appreciated.

Travelerswa

 

[Guest]

Hi

Just remove the quotation marks and = sign around P26

=SUMIF(O25:O55, P26,J25:J55)

 

[olasa]

It's hard to say why C does not work.
So instead I'll send over a link to a Workbook (zip-file) that works.

HTH
Ola Sandström


Attachment
http://www.excelforum.com/attachment.php?attachmentid=3622&stc=

+-------------------------------------------------------------------
|Filename: Book5.zip
|Download: http://www.excelforum.com/attachment.php?postid=3622
+-------------------------------------------------------------------

 

[Guest]

Part C?

Please elaborate

 

[travelersway]

THANK YOU ALL !

It works fine now. Olasa, thank you for the fill. That gave me an ide
as to why it wasn,t functioning.

Have a great day.
Travelerswa

 

[olasa]

Happy to hear.
Thanks for the feedback
Ola Sandström

 

[vaporinc]

I have a question about the SUMIF function. I'm not sure if Excel i
capable of doing buy, I want it to return values in a specified range.

For example,
=SUMIF(A1:A20,">1",B1:B20)

However, what I would like to do is only return values that are >1 an
<200. Is it possible to do this?

Thanks,
Tro

 

[swatsp0p]

Try this:

=SUMIF(A1:A20,">1",B1:B20)-SUMIF(A1:A20,">=200",B1:B20)

Does this work for you?

Bruce