basic excel question

5ingh · 15 Sep 2008 at 15:40

yo,

never really used excel functions that much before, so i hope you can help me with something simple (i hope). i want to be able to calculate the total for a given 'text value' into a single cell.

image:

so something like: =if(A:A="banana", then calculate total cost for every banana into a single cell (35p).

thanks!

Russinating · 15 Sep 2008 at 15:42

I believe you need to use an array which I don't know anything about. Give it a google.

Usually I bodge it by having a few columns in a seperate data sheet for each option.

ie:

Column A
=IF(FruitSheet!$A2="Banana",1,0)

Column B
=IF(DataSheet!$A1="1",FruitSheet!$B2,0)

And have that for each fruit then just total the columns.

I should really learn excel properly!

5ingh · 15 Sep 2008 at 16:23

cheers for the reply,

googled excel arrays and after abit of tweaking i'm getting what i need!

=SUM((A2:A9999="banana")*(B2:B9999))
(press f2 on the formula cell, ctrl+shift+enter)
=£0.35

it doesn't seem to like multiple datatypes in a single column though (the headings) - hence the reason why ive started with a2/b2 instead of a:a / b:b

SimonCHere · 16 Sep 2008 at 11:02

How about the very simple sumif() function which does what you want.

type the following in the cells

B8 = bannana
B9 = apple
B10 = orange

D8 = =SUMIF($A$2:$A$5,$B8,$B$2:$B$5)
D9 = =SUMIF($A$2:$A$5,$B9,$B$2:$B$5)
D10 = =SUMIF($A$2:$A$5,$B10,$B$2:$B$5)

OR

=SUMIF($A$2:$A$5,"bannana",$B$2:$B$5)

anywhere you like!