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


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

B.) 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.

C.) 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öm

[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.

Travelersway

[Kassie]

Hi

Just remove the quotation marks and = sign around P26

=SUMIF(O25:O55, P26,J25:J55)
--
[email protected]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
>
>

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

[Kassie]

Part C?

Please elaborate
--
[email protected]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
>
>

[travelersway]

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

[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 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

[swatsp0p]

Try this:

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

Does this work for you?

Bruce