Date against countifs with multiple line item

[silambarasan.J]

Excel Legends,

Data:

Bill No***********Dispatch Date*******Batch NUmber

1848************19/12/2015*********35

1849************19/12/2015*********35

1850************19/12/2015*********35

1859************19/12/2015*********45

1865************19/12/2015*********46

1869************19/12/2015*********46

Required Format:

Date************No of Record Passout

19/12/2015************11


Query-2:

Date***********Starting Number*********End Number

19/12/2015******35********************46


Query-1:

No of records pass out from particular day against multiple line item.

Query-2:

To End of Date staring Batch No and End of Number.

file attached please help me.

[Glenn Kennedy]

For query 1, this will do it:
=SUM(INDEX(($B$2:$B$61=$H3)/COUNTIFS($B$2:$B$61,$B$2:$B$61,$C$2:$C$61,$C$2:$C$61),0))

[samba_ravi]

Try below array formulas (Confirm with Shift+Ctrl+Enter)
i3=SUM((FREQUENCY(IF($B$2:$B$61=$H3,$C$2:$C$61+0),IF($B$2:$B$61=$H3,$C$2:$C$61+0))>0)+0)
i4=SUM((FREQUENCY(IF($B$2:$B$61=$H4,$C$2:$C$61+0),IF($B$2:$B$61=$H4,$C$2:$C$61+0))>0)+0)
l2=MIN(IF($B$2:$B$61=MAX($B$2:$B$61),$C$2:$C$61+0))
l3=MAX(IF($B$2:$B$61=MAX($B$2:$B$61),$C$2:$C$61+0))
see the attached file

[Glenn Kennedy]

For query 2, I was a little confused by your request. This wil give the first and last for each date:

=MIN(IF($B$2:$B$61=$H3,0+$C$2:$C$61))

see sheet. This is an array formula. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Vandini.S]

Glenn Kennedy

THANK YOU SO MUCH FORMULAS WORKING PERFECTLY.

[Vandini.S]

nflsales ,

Lot of Thanks both of formulas working as fine way.You are Great.

[Vandini.S]

Glenn Kennedy

Thanks you so much for your help and Explain about the array formula.

[Vandini.S]

Glenn Kennedy

Thanks you so much for your help and Explain about the array formula.