Excel/open office spreadsheet help required

SoozyJ22

Hi everyone, I hope someone here can help me as all my usual excel experts have gone awol.

I've made a spreadsheet in in open office to record my prize wins this year. Really basic format: Date of win, Description of prize, Value, and whether online or offline.

Now what I really want is to be able to get two subtotals for my online and offline winnings. Ie one sum of all the values in rows where the online/offline column is "online" and another for the rows where that column contains the word "offline". Is this possible and if so how?

Thanks very much, Susie.

Me_Myself_2

Use COUNTIF.

MercilessKiller

ye Countif will do it.

The other is use a standard if and have a hidden cell table which has:

Online - 1
Offline - 2

where online and offlin are in one column on 2 rows and same with 1 and 2

Then you can use an if using the number that online or offline relates to .. hope that makes sense lol

Mobeer

Use SUMIF to add up your winnings. You can add values in one column based upon whether the value in another column matches some criteria.

Use COUNTIF to count values based upon some criteria.

SoozyJ22

Thanks guys. Sorry for being stupid, but could you provide me an example of the right syntax? I can't quite work it out.

MercilessKiller

SUMIF:
http://www.techonthenet.com/excel/formulas/sumif.php

COUNTIF:
http://www.techonthenet.com/excel/formulas/countif.php

Unless someone can correct me I don't believe you can use a WORD within the if argument. For example instead of >= 2001 in the example above, using == ONLINE or == Offline.

The only way to do it is to use a number for online and a number for offline (1 for online 0 for offline) then use that number in the argument rather than the word online or offline

SoozyJ22

Thanks for the link - the worked example is really handy. I'm using 1/0 for online/offline now but I just can't get it work. It just says "#NAME?' and I've done it just like the example.

Me_Myself_2

e.g.

COUNTIF(D1:D16,"OFFLINE")
COUNTIF(D1:D16,"ONLINE")

D1 to D16 are presumably all either online or offline

The grinning faces are meant to be colons!!!!!

Sorry I misread the first post, Spotter's answer below is great.

spotter

win values in C2:C4
on/offline in D2:D4

sum of online wins  =SUMIF(D2:D4,"online",C2:C4)

sum of offline wins  =SUMIF(D2:D4,"offline",C2:C4)

SoozyJ22

Thanks everyone. I've finally got it working It seems you need to use semi colons instead of commas in open office. I can't get it to work using "online" and "offline" either, but a value of 0 or 1 is working fine (would you believe it but I'm actually a computer programmer so am used to binary flags).

SoozyJ22

OK, very wierdly, I can use "Offline" but "Online" doesn't work, so I've change "Online" to "<>Offline" and that works!

Thanks so much everyone :beer: