Countifs or Index Match? Trying to Count how many times it happen in a date

What I am trying to Achieve is something like Q2, Obviously the formula is wrong cause all I got was zero.

I want to know the formula for Counting how many passes/Fails In that certain Date.

And is it possible if I typed in a new date like 09-10-16 ( Highlighted Blue ) to be automatically updated In that empty High lighted Blue Cell?

Could someone reply with the lines for it?

Originally Posted by InternSeekingHelp

All i know is to count the number of times it appeared regardless of pass/fail.

=countif($M2:$M18, O2)

Hi,

Use COUNTIFS

Originally Posted by AliGW

We can't work with images. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.



Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

There you go

Put this formula in G2:

=COUNTIFS($C:$C,$E2,$B:$B,G$1)

Copy it into H2, then copy both formulae down as far as you need to.

Hope this helps.

Pete

EDIT: I was not able to open your After file.

Originally Posted by InternSeekingHelp

Countifs for? I tried Countifs for searching the number of passes on a certain date. But i dont really know how to use the function properly. ( Keeps giving me Error )

If it isnt too much trouble, could you type out the line?

Hi,

I hope Pete_UK's solution worked for your counting issue..
I've modified the file for showing the random date in the list ( Using a helper column)
This solution was actually posted by Pete_UK in another thread.All credit goes to him.

In D2, =IF(COUNTIF($C$1:C2,C2)=1,MAX($D$1:D1)+1,"") and drag down
And in E2, =IFERROR(INDEX($C$2:$C$17,MATCH((ROW(2:2)-1),$D$2:$D$17,0)),"") and drag down.


Great solution Pete_UK!! Couldn't acknowledge in that thread!!

Originally Posted by Pete_UK

Put this formula in G2:

=COUNTIFS($C:$C,$E2,$B:$B,G$1)

Copy it into H2, then copy both formulae down as far as you need to.

Hope this helps.

Pete

EDIT: I was not able to open your After file.

Thanks Pete! i manage to Get exactly what i wanted with a tiny bit of altering in the formula, but i wouldn't even get this far without your help!

Originally Posted by chullan88

Hi,

I hope Pete_UK's solution worked for your counting issue..
I've modified the file for showing the random date in the list ( Using a helper column)
This solution was actually posted by Pete_UK in another thread.All credit goes to him.

In D2, =IF(COUNTIF($C$1:C2,C2)=1,MAX($D$1:D1)+1,"") and drag down
And in E2, =IFERROR(INDEX($C$2:$C$17,MATCH((ROW(2:2)-1),$D$2:$D$17,0)),"") and drag down.


Great solution Pete_UK!! Couldn't acknowledge in that thread!!

My biggest Gratitude to you sir.

'Not sure why the file is unable to be opened... i use Excel 2007'

Originally Posted by InternSeekingHelp

My biggest Gratitude to you sir.

'Not sure why the file is unable to be opened... i use Excel 2007'

Hi,
Happy that I could be of some help
If your problem is solved,please add Rep for all the members who've helped you.

Thanks!!

Originally Posted by chullan88

Hi,
Happy that I could be of some help
If your problem is solved,please add Rep for all the members who've helped you.

Thanks!!

Will do.

Again sorry for the trouble but i still have two more Issues.

Under Column F ( Times Conducted ) those Cells without a date tend to show weird numbers instead of showing Zero

And is it possible to link the data from one workbook to another? Because the file i sent is just a sample.

The real thing is something Like Columns B & C in another seperate Workbook. I am not sure if Paste Link will work on a Cell with compicated Formulas

Originally Posted by InternSeekingHelp

Will do.

Again sorry for the trouble but i still have two more Issues.

Under Column F ( Times Conducted ) those Cells without a date tend to show weird numbers instead of showing Zero

And is it possible to link the data from one workbook to another? Because the file i sent is just a sample.

The real thing is something Like Columns B & C in another seperate Workbook. I am not sure if Paste Link will work on a Cell with compicated Formulas

Hi,

Its showing the count of blanks in the whole range.

Try to use the following formula so that blanks would be avoided:

Formula:

Please Login or Register  to view this content.

And I've no idea about linking data between workbooks.Maybe other experts would be able to help.