Log in

No account? Create an account

Previous Entry | Next Entry

Excel is kicking my ass...

I am trying to figure out how to count a value in excel.

I have email addresses for customers and dates of transactions - about 25,000 customers, and over 60,000 lines of data. I need a countif formula that tells excel to count the email address if there is more than one transaction on the same date. Every transaction line has a unique date.

I have spend all day trying to figure this out, and my boss just told me that it was challenging (I think he's humouring me) and to go home, that we would figure it out tomorrow.

It's so frustrating because I wanted to have this done for him today. He needs it for a presentation next week and I feel like I'm really letting him down.




( 17 comments — Leave a comment )
Jul. 15th, 2011 03:33 am (UTC)
It's been while since I did anything in Excel, but...

If every line is a unique date, then am I right in thinking that if there is more than one transaction per date, then those transactions would all be on the same line? How does it indicate that there's more than one transaction per date?
Jul. 15th, 2011 05:59 am (UTC)
I compiled the data into a pivot table and the pivot has the email address; then all the transaction IDs, one per line; then the skus associated with the TIDs, one per line; then the date of the transactions, on the same line as each transaction ID.

So I could have John Doe having made 5 transactions, and what I am trying to determine is how many of those purchases are same day. I don't care if they are different days - what I want to know is how many are same day.

Does that make sense?
Jul. 15th, 2011 02:33 pm (UTC)
Let me make sure I have this straight - all your data fields are currently in the row area of the pivot, correct? If so, I think what you need to do is this:

Put the email field in the row area of the pivot first. Then put the transaction date field in the row area. Then put the transaction IDs and SKUs in the data area of the pivot, and set totals in the data area to Count.

Jul. 15th, 2011 02:42 pm (UTC)
(There are several different tweaks you can do depending on what data you want to be visible, but in general, if you want Excel to count something in a pivot table, you always want the Thing To Be Counted to be in the data area of the pivot. In this case, you aren't really counting dates, you're counting SKUs/TIDs, so that's what you want in the data area, where you can get the pivot table to do the heavy lifting for you. You shouldn't have to be writing code in a pivot table at all, you just right-click on whatever's in the data area, select Totals, and then select the type of total you want it to do - count, sum, average, etc.)
Jul. 20th, 2011 01:34 am (UTC)
I think I was actually confusing myself - well, I know I was - because the data was confusing. I was using the same data set to pull different values and then try to compare and contrast them, and part of the problem was that some of the customers fell in both single and multiple date sets if they bought 2 items one day and another item another day. So it was more like I needed a Venn Diagram as well.

*head desk* It's done now. Thank God. But thanks for the help - it was nice to have all the suggestions, cuz I was pulling my hair out!
Jul. 15th, 2011 03:42 am (UTC)
Suck it up, princess
Boss says you can go home, I say you go home!
Jul. 15th, 2011 05:59 am (UTC)
Re: Suck it up, princess

I did!
Jul. 15th, 2011 06:09 am (UTC)
Re: Suck it up, princess
Well done, Padawan.
Jul. 15th, 2011 04:18 am (UTC)
Don't know anything about Excel, thankfully. But it sounds like a useful restatement of the problem (if I understand it correctly) might be:

for each email address, count the number of times that email address is involved in more than one transaction on the same day.
Jul. 15th, 2011 06:00 am (UTC)
for each email address, count the number of times that email address is involved in more than one transaction on the same day.

That is exactly what I am trying to do! But I can't figure out a countif formula with the right variables to screen for dates.

Jul. 15th, 2011 12:27 pm (UTC)
Maybe you don't need a countif. You could do a simple count: set a counter to 1 for the first transaction date for an email address, then move to the next line and compare values. If identical, increment a counter. If not, set a new counter for the new date value, and iterate. When you're done, you can do a simple search for counter values >1.

This seems a bit complex for one statement in Excel, but I'm pretty certain that it can be done using Excel's functions. A bit brute-force, I'm afraid. Sorry 'bout that, Chief.
Jul. 20th, 2011 01:31 am (UTC)
It worked - I got a count of what I needed and that's all I cared about!

Jul. 15th, 2011 12:11 pm (UTC)
Maybe ... If you redo the pivot table (or do a new one) so that the email addy is the first variable on the left and the date is the second variable on the left, and do a count of the email addy in the calculation column, you could run your countif formula on the calculation column for anything greater than one.
Jul. 15th, 2011 12:15 pm (UTC)
Oh, and sorry to pop out of lurk mode so suddenly. I stumbled across your SGA fics recently and have been enjoying them a lot. :o)
Jul. 20th, 2011 01:31 am (UTC)
I did end up doing that exact thing - I had to count individual sales, multiple sales, same day multiple sales VS multiple sales over time and unique sales VS multiple sales.

Losing. My. Mind. But, it's done now. Thank god.

Also, hi! I'm glad you're enjoying the fics! :)
Jul. 15th, 2011 03:26 pm (UTC)
I really haven't scanned down to see if you solved the problem. I'm new to Excel myself but I found a couple of sites that might help. This is just for count. You can always bookmark it for later reference.

Jul. 20th, 2011 01:31 am (UTC)
Awesome - thanks for the help!
( 17 comments — Leave a comment )


Geek by Shaddyr

Latest Month

May 2019



Powered by LiveJournal.com
Designed by Tiffany Chow