Thread: logical function

Let me start by saying I am fairly new to logical functions. I have
searched a bit on this and was unable to locate an answer to the
function i would like to do.

what i would like to have a function do is add all in a row if text in
an colum is true.

here is some sample data

a b c
1cash $5.00 $45.00
2cash $45.00
3check $35.00
4credit $50.00

what i need to happen is if col a=cash then add b#+c#. This function
would need to hold true for the entire colum in the one cell.

with going off this sample data i have provided i want 1 cell to add
b1, c1, b2, c2 together because they have 'cash' is the col of 'a'

1 more note this needs to work with upto 25 rows.

Now i know i have not explained myself all that well and for that i
appologize for that. If there is any other info you need from me please
feel free to ask.

Thanks
-TheChef420

nathon.fuller@gmail.com wrote:
> Let me start by saying I am fairly new to logical functions. I have
> searched a bit on this and was unable to locate an answer to the
> function i would like to do.
>
> what i would like to have a function do is add all in a row if text in
> an colum is true.
>
> here is some sample data
>
> a b c
> 1cash $5.00 $45.00
> 2cash $45.00
> 3check $35.00
> 4credit $50.00
>
> what i need to happen is if col a=cash then add b#+c#. This function
> would need to hold true for the entire colum in the one cell.
>
> with going off this sample data i have provided i want 1 cell to add
> b1, c1, b2, c2 together because they have 'cash' is the col of 'a'
>
> 1 more note this needs to work with upto 25 rows.
>
> Now i know i have not explained myself all that well and for that i
> appologize for that. If there is any other info you need from me
> please feel free to ask.
>
> Thanks
> -TheChef420

I'm not sure to have well understood what you mean, but I think you could
use a function like this:

=SUMPRODUCT((A1:A25="cash"),(B1:B25)+(C1:C25))

Adapt the ranges to your needs

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

First of all i thank you for your reply. I greatly appreciate it. And
yeah i know what i said was a little confusing.

Looking at your array it logicly looks like it is what i am after. "IF
any item in a1-a4 equals "cash" then sum b row#,c row#" However it does
not seem to add the amounts together.

with the data:

cash $5.00 $35.00
check $35.00
credit $10.00 $25.00
cash $5.00 $45.00

and the function: =SUMPRODUCT((A1:A4="cash"),(B1:B4)+(C1:C4))

It is returning an amount of '0' It should have an amount '90'

I get the same result no matter what spreadsheet program i use. Excel
2003/open office 2.0

Another question.. Is an array what i need or a logical function?


Again thank you for your help

-TheChef420

Franz Verga wrote:
> nathon.fuller@gmail.com wrote:
> > Let me start by saying I am fairly new to logical functions. I have
> > searched a bit on this and was unable to locate an answer to the
> > function i would like to do.
> >
> > what i would like to have a function do is add all in a row if text in
> > an colum is true.
> >
> > here is some sample data
> >
> > a b c
> > 1cash $5.00 $45.00
> > 2cash $45.00
> > 3check $35.00
> > 4credit $50.00
> >
> > what i need to happen is if col a=cash then add b#+c#. This function
> > would need to hold true for the entire colum in the one cell.
> >
> > with going off this sample data i have provided i want 1 cell to add
> > b1, c1, b2, c2 together because they have 'cash' is the col of 'a'
> >
> > 1 more note this needs to work with upto 25 rows.
> >
> > Now i know i have not explained myself all that well and for that i
> > appologize for that. If there is any other info you need from me
> > please feel free to ask.
> >
> > Thanks
> > -TheChef420
>
> I'm not sure to have well understood what you mean, but I think you could
> use a function like this:
>
> =SUMPRODUCT((A1:A25="cash"),(B1:B25)+(C1:C25))
>
> Adapt the ranges to your needs
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy

Try in this way:

=SUMPRODUCT((A1:A4="cash")*((B1:B4)+(C1:C4)))


nathon.fuller@gmail.com wrote:
> First of all i thank you for your reply. I greatly appreciate it. And
> yeah i know what i said was a little confusing.
>
> Looking at your array it logicly looks like it is what i am after. "IF
> any item in a1-a4 equals "cash" then sum b row#,c row#" However it
> does not seem to add the amounts together.
>
> with the data:
>
> cash $5.00 $35.00
> check $35.00
> credit $10.00 $25.00
> cash $5.00 $45.00
>
> and the function: =SUMPRODUCT((A1:A4="cash"),(B1:B4)+(C1:C4))
>
> It is returning an amount of '0' It should have an amount '90'
>
> I get the same result no matter what spreadsheet program i use. Excel
> 2003/open office 2.0
>
> Another question.. Is an array what i need or a logical function?
>
>
> Again thank you for your help
>
> -TheChef420
>
> Franz Verga wrote:
>> nathon.fuller@gmail.com wrote:
>>> Let me start by saying I am fairly new to logical functions. I have
>>> searched a bit on this and was unable to locate an answer to the
>>> function i would like to do.
>>>
>>> what i would like to have a function do is add all in a row if text
>>> in an colum is true.
>>>
>>> here is some sample data
>>>
>>> a b c
>>> 1cash $5.00 $45.00
>>> 2cash $45.00
>>> 3check $35.00
>>> 4credit $50.00
>>>
>>> what i need to happen is if col a=cash then add b#+c#. This function
>>> would need to hold true for the entire colum in the one cell.
>>>
>>> with going off this sample data i have provided i want 1 cell to add
>>> b1, c1, b2, c2 together because they have 'cash' is the col of 'a'
>>>
>>> 1 more note this needs to work with upto 25 rows.
>>>
>>> Now i know i have not explained myself all that well and for that i
>>> appologize for that. If there is any other info you need from me
>>> please feel free to ask.
>>>
>>> Thanks
>>> -TheChef420
>>
>> I'm not sure to have well understood what you mean, but I think you
>> could use a function like this:
>>
>> =SUMPRODUCT((A1:A25="cash"),(B1:B25)+(C1:C25))
>>
>> Adapt the ranges to your needs
>>
>> --
>> Hope I helped you.
>>
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

grazie tanto

baci ;-)


Thank you much. I greatly appreciate it. It works perfectly!


Franz Verga wrote:
> Try in this way:
>
> =SUMPRODUCT((A1:A4="cash")*((B1:B4)+(C1:C4)))
>
>
> nathon.fuller@gmail.com wrote:
> > First of all i thank you for your reply. I greatly appreciate it. And
> > yeah i know what i said was a little confusing.
> >
> > Looking at your array it logicly looks like it is what i am after. "IF
> > any item in a1-a4 equals "cash" then sum b row#,c row#" However it
> > does not seem to add the amounts together.
> >
> > with the data:
> >
> > cash $5.00 $35.00
> > check $35.00
> > credit $10.00 $25.00
> > cash $5.00 $45.00
> >
> > and the function: =SUMPRODUCT((A1:A4="cash"),(B1:B4)+(C1:C4))
> >
> > It is returning an amount of '0' It should have an amount '90'
> >
> > I get the same result no matter what spreadsheet program i use. Excel
> > 2003/open office 2.0
> >
> > Another question.. Is an array what i need or a logical function?
> >
> >
> > Again thank you for your help
> >
> > -TheChef420
> >
> > Franz Verga wrote:
> >> nathon.fuller@gmail.com wrote:
> >>> Let me start by saying I am fairly new to logical functions. I have
> >>> searched a bit on this and was unable to locate an answer to the
> >>> function i would like to do.
> >>>
> >>> what i would like to have a function do is add all in a row if text
> >>> in an colum is true.
> >>>
> >>> here is some sample data
> >>>
> >>> a b c
> >>> 1cash $5.00 $45.00
> >>> 2cash $45.00
> >>> 3check $35.00
> >>> 4credit $50.00
> >>>
> >>> what i need to happen is if col a=cash then add b#+c#. This function
> >>> would need to hold true for the entire colum in the one cell.
> >>>
> >>> with going off this sample data i have provided i want 1 cell to add
> >>> b1, c1, b2, c2 together because they have 'cash' is the col of 'a'
> >>>
> >>> 1 more note this needs to work with upto 25 rows.
> >>>
> >>> Now i know i have not explained myself all that well and for that i
> >>> appologize for that. If there is any other info you need from me
> >>> please feel free to ask.
> >>>
> >>> Thanks
> >>> -TheChef420
> >>
> >> I'm not sure to have well understood what you mean, but I think you
> >> could use a function like this:
> >>
> >> =SUMPRODUCT((A1:A25="cash"),(B1:B25)+(C1:C25))
> >>
> >> Adapt the ranges to your needs
> >>
> >> --
> >> Hope I helped you.
> >>
> >> Thanks in advance for your feedback.
> >>
> >> Ciao
> >>
> >> Franz Verga from Italy
>
> --
> (I'm not sure of names of menus, options and commands, because
> translating from the Italian version of Excel...)
>
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy