Spreadsheet Formula Help

StayOrGo

Hi guys.

I know this isn't strictly poker related but I was wondering if someone could assist me.




On the spreadsheet above, I have manually calculated the totals in columns C14, C15 and C16. (The bottom three cells of column C)

I would like to be able to automate this. For example, I would like to sum the totals of column C where column B = "MEMBERSHIP" and put the total automatically in cell C14. Same principle for "PRACTICE" and "LEAGUE" in Cells C15 and C16.

My local Table Tennis club have asked me to assist with their accounts, so would really appreciate any help from "spreadsheet guru's" out there.

I think I can do it using a "SUM IF" but not sure how to when the criteria is in column B and the value in column C.

Hope this makes sense.

Cheers,

Graham

mumsie

Hi Graham, is this excel or google sheets?

mumsie

Im away from PC right now, on my mobile.

If its google sheets , something along this line...

=SUMIF(B3:B18,"MEMBERSHIP")

If you don't solve it meanwhile, ill be home later.

TommyD

If this is Excel the formula is =sumif(B:B,"MEMBERSHIP",C:C). Overall sumif template is =sumif([The range we are filtering the criteria],[The Criteria],[The range we are summing the filtered results from]).


If you are using Excel feel free to fire as many questions as you want at me.

StayOrGo

The trial one I am doing is in Excel, but the real one will be in GOOGLE SHEETS. (Hopefully should be the same formula)

I have tried the above suggestions, but must be doing something wrong.

Tommy, I have you email address, so have sent you the test spreadsheet over, hope you don't mind.

Cheers,

Graham

bbMike

At a quick glance I think using Tommy's formula you'll get a circular reference error as you'll be including the rows you're inputting into in the range.

If you're going to add new entries I'd move your totals to the top and do something like =SUMIF($B$4:$B$500,"MEMBERSHIP",$C$4:$C$500)

The dollars just hold your range references in place if you copy the formula elsewhere.

On that, if you instead just type MEMBERSHIP, PRACTICE, LEAGUE (without the "TOTAL"), then you could use the cell reference to the left of the total you're calculating, then copy it down.

e.g. if you leave everything where it is it would be

=SUMIF($B$4:$B$9,C15,$C$4:$C$9)

StayOrGo

Actually, will try and post actual spreadsheet here:

No, can't do it. Won't allow file format.

TommyD

bbMike said:

At a quick glance I think using Tommy's formula you'll get a circular reference error as you'll be including the rows you're inputting into in the range.

If you're going to add new entries I'd move your totals to the top and do something like =SUMIF($B$4:$B$500,"MEMBERSHIP",$C$4:$C$500)

The dollars just hold your range references in place if you copy the formula elsewhere.

On that, if you instead just type MEMBERSHIP, PRACTICE, LEAGUE (without the "TOTAL"), then you could use the cell reference to the left of the total you're calculating, then copy it down.

e.g. if you leave everything where it is it would be

=SUMIF($B$4:$B$9,C15,$C$4:$C$9)

You don't get the circular reference provided nothing in the summary total section meets the criteria. But I get where you're coming from, having summaries in the same column as the raw data was always a little annoying when I had to draw these up, but I think I've done a workaround which should mean you don't need to split the sheet and will work as more rows are populated.

I agree with typing in the term to help cell referencing as more are added, I've put this in white text in Column A, it will automatically pop up as '[TERM] TOTAL' in B and will look from Column A to add from the raw data and plonk it in the corresponding cell in Column C.

Fingers crossed, should work like a charm.

mumsie

Here you go...

https://goo.gl/eFuuRK

mumsie

These are the google sheets lines

=sumif(B3:B8, "membership",C3:C8)
=sumif(B3:B8, "practice",C3:C8)
=sumif(B3:B8, "league",C3:C8)

StayOrGo

Many thanks MUMSIE, TOMMYD, BBMIKE, for your combined assistance.

I am happy to say, I have implemented it successfully on the live data.

You really are top fellas!

You've gotta love the SKY Community. TY