# Posts by Fin Fang Foom

Re: Top 5 values with corresponding data containing identical values

Hi Beaczar,

I'm not sure this what you are looking for, If not maybe someone else can help you out.

In cell G1 input max value 5

H2,

=COUNTIF(B2:B26,">="&LARGE(B2:B26,G1))

I2, copied down:

=IF(ROWS(I\$2:I2)<=\$H\$2,LARGE(\$B\$2:\$B\$26,ROWS(I\$2:I2)),"")

J2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(I2<>"",INDEX(\$A\$2:\$A\$26,SMALL(IF(\$B\$2:\$B\$26=I2,ROW(\$B\$2:\$B\$26)-ROW(\$B\$2)+1),COUNTIF(\$I\$2:I2,I2))),"")

I uploaded a sample file for you.

## Files

Re: Top 5 values with corresponding data containing identical values

Quote from NBVC;631898

Not sure if it is clear... so if you select just from E2:F6 and chart that, doesn't it work?

I believe he wants a dynamic chart.

Re: Lookup Value Across Multiple Worksheets And Retrieve Worksheet Name

Hi,

This is the first time in 4 years I've done this.

I couldn't get exactly what you want. But the formula below will display multiple result across cells.

Input formula in cell C2 copy across and down.

=IFERROR(IF(COLUMNS(\$C\$2:C2)<=\$B2,INDEX(XWSLST,SMALL(IF(Col_A=\$B2,S+1),COLUMNS(\$C2:C2))),""),"")

Now if you want multiple results in one cell then VBA or a 3rd part add-in will do the job. unfortunately I don't have the experience to help you on that.

## Files

Re: Display Top/Bottom X In Chart/Graph

Thanks Domenic it great!!!

Thank you so much.

Re: Display Top/Bottom X In Chart/Graph

Hi Domenic,

Thanks it looks great. Can I have a second criteria with your formulas. Example below:

Look in worksheet 2

## Files

Re: Display Top/Bottom X In Chart/Graph

Quote from Domenic

Fin,

The whole point of the originating thread was to create a chart without the use of additional cells, columns, formulas, and pivottables. In other words, to create a chart based solely on the source data. Is that your intention?.

Yes, Some of the users don't know how to use pivottables and it probably be easier for them just entering the kth number in a paticular cell and have the chart adjust accordanly. Is that possible?

Quote from Domenic

Otherwise, as Dave as already mentioned, the pivottable is the easiest and most efficient way of generating a chart.

Yes I like Dave's solution.

Re: Countif Across Multiple Worksheets

Maybe...

=INDEX(FREQUENCY('*'!B12,{-1000000,0.5}),2)

Hope it helps!

Re: Count/Sum Unique Values/Entries

Thanks for the interest Fin Fang Foom

I believe that both versions will work......unless you know different :question:

The 1 gives me a 0 in the DCOUNT formula but when I changed it to a zero at the end of the Sumproduct your formula works perfectly.

Re: Count/Sum Unique Values/Entries

=SUMPRODUCT((B\$2:B2>=date1)*(B\$2:B2<=date2)*(H\$2:H2=text1)*(C\$2:C2=C2))=1

Shouldn't the formula at the end of the Sumproduct have a zero so the DCOUNT could work properly?

Re: Chart Text Strings &amp; Sum Up Values And Display It In A Descending Order On Chart

Quote from Dave Hawley

I don't know, is it? I would use AutoFilter or a PivotTable to show the top/bottom x.

Thanks Dave I did not know a pivottable could do that.

Thanks its perfect!

P.S. If there is a formula version it's still be interesting to see how its done.

Hi everyone,

Just following the rules so I'm posting another thread. This is a second question from a original posting I did earlier.

If I change the chart type to columns is it possilble to show the top 3 in a descending order in the chart?

Re: Create Chart Base On Text Strings And Sum Up Values

Quote from Domenic

Try changing the reference for Array2 to the following...

=SUMIF(DynRange,Array1,OFFSET(DynRange,0,1))

Hope this helps!

Thank you so much its perfect.

Thanks Domenic!

Hi everyone,

Domenic provied a solution that it will graph text strings in column A and it will display in the pie chart by using Define name range formulas. My dilema is I would like a second condition in those formulas. I would like the formula look in column A and sum up the values in column B base in column A and display it in the pie chart.

## Files

Re: Lookup Multiple Similar Values

Try...

=INDEX('Monthly current budgets'!\$E\$1:\$E\$72,MATCH(1,('Monthly current budgets'!\$A\$1:\$A\$72=\$D8)*(TRIM('Monthly current budgets'!\$B\$1:\$B\$72)=E\$5),0))

Hope it helps!

Here is a formula version.

I use a define name range formula to reference all your worksheets.

wks =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)&T(NOW())

Then input this formula in cell C4 and copy down.

Hope it helps!

## Files

Re: Extract Unique Values Between 2 Worksheets

=SMALL(IF(FREQUENCY('Sheet1:Sheet2'!\$A\$2:\$A\$10,ROW(\$1:\$10000)),ROW(\$1:\$10000)),ROWS(\$B\$2:\$B2))

Input formula in Sheet3 of cell B2 and copy down.

The formula is an-array double click inside the cell and hit,

Ctrl,Shift,Enter

Re: Count By Criteria But Only Non-Contiguous Occurences

Here is a couple of ways.

This will count every other cell in column A
=SUMPRODUCT(--(MOD(ROW(A2:A20)-ROW(A2)+1-(A2:A20="S"),2)=0),--(A2:A20<>""))

If your ranges are scattered then you could try this formula.

=SUM(COUNTIF(INDIRECT({"B20","D2","G15"}),"S"))

Hope it helps!

Re: Consolidate By Time From Different Worksheets `

I notice the calculation is very slow. Then might want to use this formula below instead, its much more efficient but it will not return in a sorted matter.

Input formula in cell A2 and copy down.

=INDEX(Col_D,MATCH(0,IF(Col_D<>0,COUNTIF(\$A\$1:A1,Col_D)),0))

The formula is an-array must hold down:

Ctrl,Shift,Enter

Hope it helps!

Re: Consolidate By Time From Different Worksheets `

Quote from Shree

Actually all I am looking for is I just want all the project nos. to be listed in column A of the worksheet "Project Nos".

Then try inputing formula in cell A2 and copy down.

=INDEX(Col_D,MATCH(SMALL(IF(Col_D<>0,IF(MATCH(Col_D,Col_D,0)=S+1,MMULT((Col_D>TRANSPOSE(Col_D))+0,(S+1)^0))),ROWS(\$A\$2:A2)),MMULT((Col_D>TRANSPOSE(Col_D))+0,(S+1)^0),0))

The formula is an-array must hold down:

Ctrl,Shift,Enter

## Files

Re: Consolidate By Time From Different Worksheets `

Hopefully I got it right.

List your worksheets in worksheet "Project Nos" in column C