Using conditions in the Subtotals function

[Nathangdavidson]

I've got circa 20k rows of data I'm analysing - it's activity with our clients over the last few years.

I've used the Subtotals function to calculate Sums, Counts and Max values.

But I'm stuck on two things.

1 - I want to show the last time we received a payment from a client - to do this I need to link the date and pay out amount fields. Not sure if this is possible?

2 - I've also got a field that has text values. I want to return the text that appears most often for that client - e.g. Yes or No.

I hope you can point me in the right direction.

thanks!

 

[T. Valko]

I've got circa 20k rows of data I'm analysing - it's activity with our clients over the last few years.

I've used the Subtotals function to calculate Sums, Counts and Max values.

But I'm stuck on two things.

1 - I want to show the last time we received a payment from a client - to do this I need to link the date and pay out amount fields. Not sure if this is possible?

2 - I've also got a field that has text values. I want to return the text that appears most often for that client - e.g. Yes or No.

I hope you can point me in the right direction.

thanks!

Need more info. What are the range addresses for all this data?

For #1, need a more detailed explanation.

For #2, will you be filtering on the client or does the client need to be a variable in the formula?

 

[Nathangdavidson]

Thanks for your swift response.

I understand you need more info to diagnose - however I'm not sure what you mean by 'range addresses for this data'. I haven't created any ranges for the data - perhaps that's where I'm going wrong

Can I attach some sample data to a thread? It might be the easiest way for me to explain what I'm trying to achieve by illustrating it.

 

[T. Valko]

Thanks for your swift response.

I understand you need more info to diagnose - however I'm not sure what you mean by 'range addresses for this data'. I haven't created any ranges for the data - perhaps that's where I'm going wrong

Can I attach some sample data to a thread? It might be the easiest way for me to explain what I'm trying to achieve by illustrating it.

The best way to post data is using one of these methods:

Excel Jeanie add-in

MrExcel HTML Maker add-in

Borders-Copy-Paste

 

[Nathangdavidson]

Thanks for the suggestions - I can't upload the data in the ways you suggest unfortuantely.

Not sure where to go from here.

Are there any tips or trickets for how to deal with large quantities of data with a variable amount of information from each customer? I think I may be going about it in the wrong way.

 

[T. Valko]

Thanks for the suggestions - I can't upload the data in the ways you suggest unfortuantely.

Not sure where to go from here.

Are there any tips or trickets for how to deal with large quantities of data with a variable amount of information from each customer? I think I may be going about it in the wrong way.

You can put together a SMALL sample file that demonstrates your problem and upload it to a free file hosting site. Then you post a link to the file in a reply. Anyone interested can then download the file and try to work out a solution.

There are many free file hosting sites to choose from.