Posts by NBVC

Check your P range. It needs to be consistent with the F range.

We are not supposed to work with email outside forum. Try saving ad .XLSX and try using attachment tool

Don't see any attachment.

You also have to adjust the ranges to suit your data

Did you confirm it with CTRL+SHIFT+ENTER?

If still can't get it then attach it to this thread

You can add more conditions to the formula...

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100<30,$P$2:$P$100)))

=MEDIAN(IF(MONTH($F$2:$F$100)=10,IF($P$2:$P$100>=30,IF($P$2:$P$100<60,$P$2:$P$100))))

etc.

Does this work?

=INDEX('Annual Totals'!B8:ED10,SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(ROW('Annual Totals'!B8:ED10)-7)),SUMPRODUCT((ISNUMBER(SEARCH(HLOOKUP(I5,'Annual Totals'!A5:EB10,(3+Cheque!G5),FALSE),'Annual Totals'!B8:ED10)))*(COLUMN('Annual Totals'!B8:ED10)-1))+1)

So the result of the nested HLOOKUP could be in any cell withing B8:ED10 or is it in a specific column only?

You'll have to use an array formula (confirmed with CTRL+SHIFT+ENTER, not just ENTER).

e.g.

=MEDIAN(IF(MONTH($A$2:$A$100)=10,$B$2:$B$100))

where A2:A100 contain dates (formula above looks for 10th month. i.e. October), and B2:B100 contain values to find median of

What's the current HLookup formula?

If you use index Match, you can add +1 to the Match portion of the formula to adjust to next column...

e.g. =INDEX(B8:ED10,MATCH(A1,B8:B10,0),MATCH(A2,E8:ED10,0)+1)

Try:

=[@Mean]-(SUM(SUMIFS([Mean],[35S],[@35S],[v.Con],"NSB",[D1],IF([@D1]="","",[@D1]))))

I have been made aware that you have also cross posted this question on another forum. Please indicate by adding the link here. We can't help you otherwise as it is against the rules.

Can you give real examples to see if there is some one of distinguishing the field header from the data?

What is between Data1 and Field2? Is there anything of consistency that can be used to separate the fields?

We've been advised that you are still cross posting on other forums.

If you want help on this, and any other forum, as they all have the same rules, then please comply to the rules of cross-posting.

Create a couple of helper columns adjacent to the main list.

These columns can be hidden.

In F4: =IF(E4="","",RAND()) copied down the full 100 rows

In G4: =IFERROR(RANK(F4,$F$4:$F$104),"") copied down the full 100 rows.

Now, in I4: =INDEX($C$4:$C$104,MATCH(ROWS(I$4:I4),$G$4:$G$104,0)) copied down 3 rows.

Adjust formulas to suit your actual data ranges.

Assuming you enter that value in B1, then try:

=COUNTIF(INDEX(A2:A5000,B1-1):A5000,">12")

Try:

=SUMPRODUCT((Data!$A$1:$A$11>=$B$1)*(Data!$A$1:$A$11<=$B$2)*(Data!$B$1:$B$11=B$4)*(Data!$C$1:$C$11=$A5)*(Data!$D$1:$D$11<>0)/(COUNTIFS(Data!$A$1:$A$11,Data!$A$1:$A$11&"",Data!$B$1:$B$11,Data!$B$1:$B$11&"",Data!$C$1:$C$11,Data!$C$1:$C$11&"")))

Try this formula in Output sheet, B5:

=SUMPRODUCT((Data!$A$2:$A$11>=$B$1)*(Data!$A$2:$A$11<=$B$2)*(Data!$B$2:$B$11=B$4)*(Data!$C$2:$C$11=$A5)*(Data!$D$2:$D$11<>0)/(COUNTIFS(Data!$A$2:$A$11,Data!$A$2:$A$11&"",Data!$B$2:$B$11,Data!$B$2:$B$11&"",Data!$C$2:$C$11,Data!$C$2:$C$11,Data!$D$2:$D$11,Data!$D$2:$D$11&"")))

Copied down and across.