Sumif
 

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

=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


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=389858

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?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=389858

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öm


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=389858

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.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=389858

Hi

Just remove the quotation marks and = sign around P26

=SUMIF(O25:O55, P26,J25:J55)
--
ve_2nd_at. Randburg, Gauteng, South Africa


"travelersway" wrote:


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

=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


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=389858

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...tid=3622&stc=1


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=389858

Part C?

Please elaborate
--
ve_2nd_at. Randburg, Gauteng, South Africa


"travelersway" wrote:


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.

Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=389858

THANK YOU ALL !

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

Have a great day.
Travelersway


--
travelersway
------------------------------------------------------------------------
travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623
View this thread: http://www.excelforum.com/showthread...hreadid=389858

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


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=389858

I have a question about the SUMIF function. I'm not sure if Excel is
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 and
<200. Is it possible to do this?

Thanks,
Troy


--
vaporinc
------------------------------------------------------------------------
vaporinc's Profile: http://www.excelforum.com/member.php...o&userid=25717
View this thread: http://www.excelforum.com/showthread...hreadid=389858

Try this:

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

Does this work for you?

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=389858