Excel Geek Required! Sumif and Autofilter

[Pheebs]

Hey hey HEY!

I need help..

Annnnnd I need help on excel (before any of you lot got in there!)

I have looked up on various google links the below but it just does not make any sense to me and... despite it's not that important... the fact I cannot do it is annoying me so much I now need to do it ;)

I'll simplify it... so what I have is as follows:

Columns of:

(A1)Description - (B1)Client - (C1)Total - (D1)Paid (Yes/No)

Now the columns have been autofiltered so I can look at my clients (say Bill, Sarah and Joe) separately.

Firstly, what I wanted was to be able to add up all monies paid separately and all the monies outstanding separately.

Sooo I did the formula below for all monies paid:

=SUMIF(D2: D10,"Yes",C2:C10)

Annnnd I did the formula below for all monies outstanding:

=SUMIF(D2: D10,"No",C2:C10)

Simple, works a treat (note put a gap between ":" and "D10" on here but not on because if I don't this little fella pops up: :D! Heeh!). HOWEVER! I now want to be to select a specific client from my autofilter (say Sarah) and do the above... but I don't know how to do this. If I select Sarah on the autofilter, the above formulas still include the data which isn't shown (ie Bill and Joes figures!).

Now I've read about subtotals and countif etc but I am now just super confused. People keep chucking random numbers in at the beginning of formulas which, I cannot see, has any relevance to the data!

How do I do this people? Can anyone decipher the interweb better than I or perhaps are just whizz kids themselves?

I uploaded the simplified version:

Stoopido Excel Spreadsheet (https://docs.google.com/open?id=0B71T20VSvFPnODU5ZTFiMzItMzdmZC00YzJlLTg2O WEtOGQ5MmJiNjg5ZmU1)

Thanks peoples :) x

[Mark]

What are you doing working in Excel at this hour? (not that I can talk having just finished compiling some C++ code).

I think Excel is doing the 'right' thing. That is that the formula should *not* take account of any filter (which is usually removing the data you don't want to see - and not changing it). I've never used filters in Excel though, so what do I know? ;D

So then, assuming I'm correct, I'll point you in a different direction - pivot tables. I haven't used them either, but nonetheless a pivot table is probably what you need to do the summary 'thing'.

[Pheebs]

Yeh it's definitely doing the right thing according to the formula... thus needing to change it.

I've read in places you can use "SUBTOTAL" and combine this with the SUMIF formula... but I don't get how when reading about it (it makes nooooooo sense to meee!)

Sam and I have been working late the past couple of weeks and it's fallen into a habit... something I must break! This is hardly work though... just something that's annoying me that hopefully I can figure out to help me with worky things ;)

Thanks for your help Mark :)

[Pheebs]

No super geek? :(

[volospian]

I'm not sure if I get what you want, but can't you just use sumifs (note the plural)?

For example, if your spreadsheet looked like this:

Description Client Total Paid
Dinner bill 10 no
Lunch jon 10 yes
Drinks alice 10 yes
Drinks bill 10 yes

Sumifs= 10

=SUMIFS(C2:C5,B2:B5,"bill",D2: D5,"yes")

the first section (C2:C5) covers the cells with the criteria (or, in this case, the totals). The second entry (B2:B5) is the first criteria range, followed by the required criteria (in this case, the list of clients, and we've selected "bill"). The next pair (D2: D5,"yes") is the second criteria (paid column, and we have selected "yes") and so on...

You could also add a third criteria to filter based on the results in the first column to see how many times "bill" had "dinner" and has paid "yes" and so on...

Does that help?

[Pheebs]

Yeh that makes sense buuut I will have multiple clients and so didn't want to have to do a SUMIF for each client. I just wanted to be able to select from the autofilter "Sarah" and have all of sarahs records come up and then have a field with all the monies related to her added up and no one else!

Directly after that I might select Tony and do the same... it's annoying I cannot figure out a way for the SUMIF to recognise only the filtered names!

[semi-pro waster]

I'm going to suggest a way that may sound a bit silly but it will prevent you messing about with increasingly complicated formula. Use pivot tables, very easy to understand and very powerful when used correctly - all you'd need to do every so often is refresh the table and ensure that it is picking up any new additions (i.e. the range is correct).

If I've got time over the next couple of days and you aren't happy with the above/haven't already got it sorted then I'll try and have a mess about with the formulas but I actually think you're probably better off not complicating it where you don't need to.

[Pheebs]

Excuse my naivety but.... pivot tables?! :)

[Mark]

Got Deja Vu? I did suggest pivot tables last night. :)

I don't think you can do what you want with a single formula, so it's time to get reading about pivot tables. I'd start with the help. :)

[semi-pro waster]

I don't know what version of Excel you've got but supposing it's 2007/10 then it's under the ribbon called Insert and then it's the first button below the menus. The range should autoselect (more or less) but do check that it's picking up all the cells, I generally use the option to put it in a new sheet.

This will then give you a blank table with a number of options that you can pick to select on. In this case I've put the names for the rows and the status for the columns with the amounts as the value fields but have a play about with it and see what layout suits you, from there you can simply pick a name from the filter and that should do what you want.

http://www.semi-pro.co.uk/Pivottable.jpg

//edit sorry Mark, I didn't read all of your post before but I think it's definitely the way to go here as even having had a bit more of a think about it there isn't going to be any simple formula that works it out that I can remember.

[Mark]

No worries! You do know how to use them, which I don't. :)

[volospian]

Yeh that makes sense buuut I will have multiple clients and so didn't want to have to do a SUMIF for each client.

lol, sorry, I was going to suggest a pivot table, but thought you wanted some kind of fixed field. :)

[Will]

<3 pivot tables :cool:

[Pheebs]

Ahh okay... I'll have a look into pivot tables then :)

The annoying thing though... I *know* there's a formula to do what I want. I'll be damned if I can figure it out though! Boo hiss!

Thanks for your help people! might be back when i get stuck!

[Rich_L]

Yeah PivotTables are designed exactly for this kind of stuff, an hour or two playing around with them will save a bunch of time in future.

However, you could also use the SUMIFS pointing to the client name contained in a cell so on top of the Autofiltered table you have:
A B C
1 Client Paid Outstanding
2

In A2 put the clients name, in B2 put =SUMIFS(C5:C14,B5:B14,A2,D5:D14,"Yes") and in C2 put =SUMIFS(C5:C14,B5:B14,A2,D5:D14,"Yes")

One advantage of this approach is that later on you can use the Advanced Filter tool to filter by the criteria in Cell A2 and extract that data to another sheet etc.

You could then do a little Macro to do a Filter based on what is in A2

Sub PheebyPhilter()
Range("A4:D20").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=*" & Range("A2").Value & "*"
End Sub


Where Range is the table with the data in, the Field value is the Column with the Client names in, Range is the client you want.

So in order to produce a report, put the Macro in your toolbar then all you have to do is change the name of the client in A2 then press the button :) You could further enhance this by having a list of all your clients and use the Data Validation tool to make the client name a drop-down list.

*edit*

Here you go, will have to enable Macros ofc, client names are on a separate sheet for validation.

http://www.mediafire.com/?69ygu86o1shrybc

[Pheebs]

Rich... your excel geekness is genius! Had a quick tinker and a few things are working (makes more sense now too). Will play around properly tonight (have to do some tiling and skirting boards today) but thank you so much!

How are you Mister?! Long time no see or hear!? How's the gorgeous lady which is your Mrs!? Hoping you're well and happy!

[Rich_L]

Cool glad to be of assistance :)

Doing great thanks, very busy with work (electrocuting people lol) so mainly lurking on here :p Wife is great too, hard to believe we've been married over 2 years already :eek: Thanks hope things are good with you sounds like you have quite the adventure lined up - pop by and say Hi if you're passing through California :D

[Pheebs]

We will be in sunny california you know! Going to try and blag an evening at the magic castle for free (geeky magicness in me!) If you're willing to see some smelly bikers then heck yeh we'll turn up and say boo!

Glad things are going well for you (the electrocuting sounds rather like a Milgram experiment! Eak!!)! Twooooo years! Blimey gov! That's kinda mega though! Keep smiling and howdy to your lady!