Recent content by Kyle McGhee

Hi, See attached. I added a column on the second tab which is hopefully OK so that I use a SUMIF approach.

Try this: =CALCULATE(DISTINCTCOUNT([UniqueIDProcessID]),FILTER(Table1,NOT(ISBLANK([UniqueIDProcessID])))) (replace Table1 with the table name in your data model)

Hi Veronica, I have a personal budget I made for myself and for split transactions, I just don't bother with the "Split" line as you have it currently laid out. For example, if I go to Costco and buy cat food, dog food and groceries, I list Costco 3 times. My suggestion is to keep it simple...

Try this: Data tab -> From Other Sources -> From Microsoft Query -> In the pop-up ensure <New Data Source> is selected and press OK 1. Enter a name for the data source 2. Select from the driver dropdown "Microsoft Text Driver (*.txt, *.csv)" 3. "Connect..." Select the directory where the file...

maybe this would help point you in the right direction? http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

Hi SirJB7 The formula I posted works on my laptop and I can't get yours to work on mine, which I believe could be due to the "@" referencing that is not a part of XL 2007 tables. I also assumed "mycol" was the first column of the table. try this one...can't test it with the @ referencing in...

something like this should work =INDEX(Table1[#All],ROW(Table1[[#This Row],[mycol]])-1,1) works as intended if the table starts in row 1 by pulling in the data in the row previous row of mycol...

Strictly based on the SQL you posted, a Cartesian Product query would provide what you describe. SELECT B.yr, B.Dept, A.Grade FROM B, A GROUP BY B.yr, B.Dept, A.Grade HAVING (((A.Grade) In (3,4,5,6,7,8))) ORDER BY B.Dept;

kaushik03, You can try this code that I did for someone else about 7 months ago, modified slightly. It updates the chart title with the X values of both series, prefixed by the x-axis label for that data point. You have to roll over the actual columns, rather than the actual x-axis. The...

henksss, The formula provided returns the same value as you are looking for, 3. My comment about the link related to the post prior to mine, not my formula. sreekhosh, thanks

Hi henksss, Array enter this formula (ctrl+shift+enter while in the formula bar): =SUMPRODUCT(--(MMULT((A1:A7),TRANSPOSE(1/(B1:B7)))=1)) The link just provided will colour the duplicates but will not count them into a cell, if that is what you need. Kyle

Hi Sajan, Based on your original post, I understand what you are trying to do, as I tried something similar but with the PRODUCT function. Unfortunately, I could not figure out how to iterate through each row of the virtual array in a single formula (or named formula) without the use of helper...

Hi xcruc1at3r Don't see any screenshots in the file you linked to. Is it bluezone that are you referring to? Kyle

Hi All, Threshold Price Price Difference 0 5 5 100 4 -1 500 3 -1 1000 2 -1 =SUMPRODUCT((B1&#62;A6:A9)*(B1-A6:A9)*(C6:C9)) Where B1 Contains the volume of units, A6:A9 is the threshold range and C6:C9 is the price difference...

AlanS1337, You could also do something like this =SUMPRODUCT((((B2:B4=&#34;A&#34;)*C2:D4)/SUM((C2:D4&#62;0)*(B2:B4=&#34;A&#34;)))) or =SUMPRODUCT((((B2:B4=&#34;A&#34;)*C2:D4)/SUM((C2:D4&#60;&#62;&#34;&#34;)*(B2:B4=&#34;A&#34;)))) Only difference between the two is the C2:D4&#62;0 and...