Excel Geek Required! Sumif and Autofilter - Page 2

Mark · 26-10-2011, 22:49

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

volospian · 27-10-2011, 09:20

Quote:

Originally Posted by 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.

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

Will · 27-10-2011, 10:19

<3 pivot tables

Pheebs · 27-10-2011, 18:16

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 · 02-11-2011, 02:22

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:

Code:

       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

Code:

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 · 03-11-2011, 12:04

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 · 03-11-2011, 17:02

Cool glad to be of assistance

Doing great thanks, very busy with work (electrocuting people lol) so mainly lurking on here

Wife is great too, hard to believe we've been married over 2 years already

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

Pheebs · 03-11-2011, 19:52

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!

27-10-2011, 10:19	#13
Will BBx woz 'ere :P Join Date: Jan 1970 Posts: 2,147,487,208	<3 pivot tables __________________ *No No!*

27-10-2011, 18:16	#14
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	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! __________________ http://www.spiffingdesigns.co.uk

02-11-2011, 02:22	#15
Rich_L Dr Cocktapuss Join Date: Jul 2006 Location: Seven Sizzles Posts: 1,044	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: Code: 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 Code: 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 __________________ Last edited by Rich_L; 02-11-2011 at 04:32.

03-11-2011, 12:04	#16
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	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! __________________ http://www.spiffingdesigns.co.uk

03-11-2011, 17:02	#17
Rich_L Dr Cocktapuss Join Date: Jul 2006 Location: Seven Sizzles Posts: 1,044	Cool glad to be of assistance Doing great thanks, very busy with work (electrocuting people lol) so mainly lurking on here Wife is great too, hard to believe we've been married over 2 years already 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 __________________

26-10-2011, 22:49	#11
Mark Screaming Orgasm Join Date: Jul 2006 Location: Newbury Posts: 15,194	No worries! You do know how to use them, which I don't.

03-11-2011, 19:52	#18
Pheebs Deep Throat Join Date: Jul 2006 Posts: 6,512	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! __________________ http://www.spiffingdesigns.co.uk