SumIf with an Or

[KarenH]

I have a SUMIF statement that doesn't appear to be working -- not throwing an
error, but not returning the correct value:

=SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)

What I'm trying to say is that if the value in column C is 61505 or 61515 or
61517, then it should sum the values in column E. I have not used a range
for C or E, just the columns, because the number of rows will change

Googling on this tells me that it's probably not possible with a SumIf --
and I tried SumProduct, but that didn't work either.

Suggestions? Thanks in advance.

[shail]

Hi Karen,

This might help you -

=SUM(IF(A10=61505,B9:B11,IF(A10=61515,B9:B11,IF(A10=61517,B9:B11))))

This is an array function so you need to press Alt+Ctrl then Enter. If
you did it correctly braces {} will appear.


Thanks,

Shail

[shail]

Hi Karen,

This might help you:

=SUM(IF(A10=61505,B9:B11,IF(A10=61515,B9:B11,IF(A10=61517,B9:B11))))

This is an array function

Thanks

Shail


KarenH wrote:

> I have a SUMIF statement that doesn't appear to be working -- not throwing an
> error, but not returning the correct value:
>
> =SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)
>
> What I'm trying to say is that if the value in column C is 61505 or 61515 or
> 61517, then it should sum the values in column E. I have not used a range
> for C or E, just the columns, because the number of rows will change
>
> Googling on this tells me that it's probably not possible with a SumIf --
> and I tried SumProduct, but that didn't work either.
>
> Suggestions? Thanks in advance.

[RagDyeR]

Try this:

=SUMPRODUCT((C1:C1000={61505,61515,61517})*E1:E1000)

With SumProduct, you *cannot* use total column references (C:C, E:E).


--

HTH,

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

"KarenH" <[email protected]> wrote in message
news:[email protected]...
I have a SUMIF statement that doesn't appear to be working -- not throwing
an
error, but not returning the correct value:

=SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)

What I'm trying to say is that if the value in column C is 61505 or 61515 or
61517, then it should sum the values in column E. I have not used a range
for C or E, just the columns, because the number of rows will change

Googling on this tells me that it's probably not possible with a SumIf --
and I tried SumProduct, but that didn't work either.

Suggestions? Thanks in advance.

[shail]

Hi RD,

Thanks for this thing. I too tried and it is working just perfect.

Thanks again

Shail


RagDyeR wrote:
> Try this:
>
> =SUMPRODUCT((C1:C1000={61505,61515,61517})*E1:E1000)
>
> With SumProduct, you *cannot* use total column references (C:C, E:E).
>
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "KarenH" <[email protected]> wrote in message
> news:[email protected]...
> I have a SUMIF statement that doesn't appear to be working -- not throwing
> an
> error, but not returning the correct value:
>
> =SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)
>
> What I'm trying to say is that if the value in column C is 61505 or 61515 or
> 61517, then it should sum the values in column E. I have not used a range
> for C or E, just the columns, because the number of rows will change
>
> Googling on this tells me that it's probably not possible with a SumIf --
> and I tried SumProduct, but that didn't work either.
>
> Suggestions? Thanks in advance.

[shail]

Hi RD,

Thanks for this thing. I too tried and it is working just perfect.

Thanks again

Shail


RagDyeR wrote:
> Try this:
>
> =SUMPRODUCT((C1:C1000={61505,61515,61517})*E1:E1000)
>
> With SumProduct, you *cannot* use total column references (C:C, E:E).
>
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "KarenH" <[email protected]> wrote in message
> news:[email protected]...
> I have a SUMIF statement that doesn't appear to be working -- not throwing
> an
> error, but not returning the correct value:
>
> =SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)
>
> What I'm trying to say is that if the value in column C is 61505 or 61515 or
> 61517, then it should sum the values in column E. I have not used a range
> for C or E, just the columns, because the number of rows will change
>
> Googling on this tells me that it's probably not possible with a SumIf --
> and I tried SumProduct, but that didn't work either.
>
> Suggestions? Thanks in advance.

[RagDyeR]

You're welcome.
--

Regards,

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

"shail" <[email protected]> wrote in message
news:[email protected]...
Hi RD,

Thanks for this thing. I too tried and it is working just perfect.

Thanks again

Shail


RagDyeR wrote:
> Try this:
>
> =SUMPRODUCT((C1:C1000={61505,61515,61517})*E1:E1000)
>
> With SumProduct, you *cannot* use total column references (C:C, E:E).
>
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "KarenH" <[email protected]> wrote in message
> news:[email protected]...
> I have a SUMIF statement that doesn't appear to be working -- not throwing
> an
> error, but not returning the correct value:
>
> =SUMIF(DataSheet!$C:$C,"61505 OR 61515 OR 61517",DataSheet!$E:$E)
>
> What I'm trying to say is that if the value in column C is 61505 or 61515
or
> 61517, then it should sum the values in column E. I have not used a range
> for C or E, just the columns, because the number of rows will change
>
> Googling on this tells me that it's probably not possible with a SumIf --
> and I tried SumProduct, but that didn't work either.
>
> Suggestions? Thanks in advance.