Alright,
I believe this is a challenge. I am trying to calculate the number of transactions that have both a service and a retail component (tickets 253 and 258 have both service and retail components), and the sum of the total revenues, using an equation (not using multiple columns and the vlookup function). For the short list, I have highlighted the transactions in yellow (see attached file), but that was calculated manually. I can do this the long way by using a vlookup function, but it is not pretty and creates a massive file. I've been experimenting with sumifs, countifs, count unique equations, so on so forth, but to no avail. I will be impressed if someone can solve this. See attachment for easy viewing.
Thank you,
Barclay
Ticket Number Service Type Charge
252 Service $40
253 Retail $33
253 Service $162
254 Retail $73
255 Retail $69
255 Retail $175
256 Service $168
257 Service $103
258 Service $62
258 Retail $127
259 Retail $51
260 Service $55
261 Service $78
262 Service $92
263 Service $95
Revenue Transactions
Retail $528 6
Service $855 9
Retail & Service $384 2
Last edited by bmcfiv; 03-12-2009 at 06:42 PM.
My instinct would be to add a further column to your data set, such that:
D6:
=--ISNUMBER(LOOKUP(2,1/(($C$6:$C$20=$C6)*($D$6:$D$20<>$D6))))
copied down to D20
Then for your summary you can use:
D26:
=SUMIF($F$6:$F$20,1,$E$6:$E$20)
E26:
=SUMPRODUCT(--($F$6:$F$20=1),1/COUNTIF($C$6:$C$20,$C$6:$C$20&""))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Try this...see attached.
HTH
@Morrigan: my jaw dropped. Wow!
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Morrigan,
Wow. That is absurd. Great work. I'm going to run on a spreadsheet with 25,000 entries tomorrow, I'll let you know how it goes.
Maybe you should slow down your excel work and focus on solving cold fusion instead.
Thanks a million,
Barclay
Here's another way...
D26, confirmed with CONTROL+SHIFT+ENTER:
=SUM(IF(ISNUMBER(MATCH(C6:C20,IF(ISNUMBER(MATCH(IF(D6:D20="Retail",C6:C20),IF(D6:D20="Service",C6:C2 0,"#"),0)),C6:C20),0)),E6:E20))
E26, confirmed with CONTROL+SHIFT+ENTER:
=SUM(IF(ISNUMBER(MATCH(IF(D6:D20="Retail",C6:C20),IF(D6:D20="Service",C6:C20,"#"),0)),1))
Hope this helps!
Guys, thank you, everything worked like a charm. Now I'm going to dissect what you wrote, and try to figure out how the formulas work with one another.
I decided to use DonkeyOte. It is the only one that is automatic and allows me to just dump the data with additional steps.
Thanks Guys - next time hopefully I can find one more challenging.
Speaking of that solution...
can someone tell me what "--" do in formulasTY
"Relax. What is mind? No matter. What is matter? Never mind!"
It turns the array into either 1 or 0. In short, when the argument is true, it returns a value of 1 otherwise it returns 0.
It appears all the formula we have provided are automatic. What do you mean they aren't?
Originally Posted by bmcfiv
Aren't the other solutions array's and require alt ctrl del to run the numbers every time a new set up data is placed?
Also, why is there a 2 in Donk's lookup function?
Thank you, may switch to single cell as a solution if I can understand the formulas.
I am just guessing...perhaps your problem is as you add new data, the range in the formula doesn't update itself? If so, what I do usually is...include an extra row before and after your data in your range. See attached.
If it's something else, you would have to explain more or I'd just go with what Donkey has suggested and use helper column.
The only time the array formula needs to be re-confirmed with CONTROL+SHIFT+ENTER is when the formula itself is edited.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks