logical function

[nathon.fuller]

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

 

[Franz Verga]

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

 

[nathon.fuller]

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]

Try in this way:

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

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

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

 

[nathon.fuller]

grazie tanto

baci ;-)


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