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: ! 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

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?

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.



//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.