Countifs formula not working

[Confusedgirl]

Column A Column B
Tests EarlyStart
FST_NANA 17/05/2016
SWCPITDIS 17/05/2016
SWCPITCOMP 18/05/2016
SWCPITDIS 18/05/2016
ZFLOW20 16/05/2016
FST_NANA 16/05/2016
POOL 16/05/2016
POOL 16/05/2016
POOL 16/05/2016
FST_NANA 18/05/2016
FST_NANA 16/05/2016


I would like to find out the number of tests that are required on each day. I have tried the calculation =COUNTIFS(Tests,D2,EarlyStart,E1) with a result of 0 which is incorrect. I've also tried =COUNTIF(Tests,D2)+COUNTIF(EarlyStart,E1). Both have not worked. Can someone please tell me where I am going wrong. It is driving me a bit crazy!!!

 

[Confusedgirl]

Hi,

Thanks for the suggestion. I tried a pivot table but since the date is replicated in different rows it is shown multiple times in a pivot table.

The best way I have found so far is the filter method. Though that's very time consuming. If you have any other ideas they would be most welcome

 

[asad]

Sir Aladin,

Just a thought .... Isn't it possible for the values to be brought in a table format using Index Match formula and dates to go on top row across the columns and then another one of your magic formulas will pick up the counts. Exactly like pivot table but with formulas.

Asad

 

[Aladin Akyurek]

Sir Aladin,

Just a thought .... Isn't it possible for the values to be brought in a table format using Index Match formula and dates to go on top row across the columns and then another one of your magic formulas will pick up the counts. Exactly like pivot table but with formulas.

Asad

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]Tests[/td][td]EarlyStart[/td][td][/td][td]Tests/Dates[/td][td]

16-May-16​

[/td][td]

17-May-16​

[/td][td]

18-May-16​

[/td][td][/td][/tr]


[tr][td]

2​

[/td][td]FST_NANA[/td][td]

17-May-16​

[/td][td][/td][td]FST_NANA[/td][td]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]


[tr][td]

3​

[/td][td]SWCPITDIS[/td][td]

17-May-16​

[/td][td][/td][td]SWCPITDIS[/td][td][/td][td]

1​

[/td][td]

1​

[/td][td][/td][/tr]


[tr][td]

4​

[/td][td]SWCPITCOMP[/td][td]

18-May-16​

[/td][td][/td][td]SWCPITCOMP[/td][td][/td][td][/td][td]

1​

[/td][td][/td][/tr]


[tr][td]

5​

[/td][td]SWCPITDIS[/td][td]

18-May-16​

[/td][td][/td][td]ZFLOW20[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]ZFLOW20[/td][td]

16-May-16​

[/td][td][/td][td]POOL[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]FST_NANA[/td][td]

16-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td]POOL[/td][td]

16-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]POOL[/td][td]

16-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td]POOL[/td][td]

16-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td]FST_NANA[/td][td]

18-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td]FST_NANA[/td][td]

16-May-16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Tests >> A2:A12

Dates >> B2:B12

Ivec >>

=ROW(Tests)-ROW(INDEX(Tests,1,1))+1

In E1 control+shift+enter, not just enter, and copy across:

=IFERROR(1/(1/MIN(IF(ISNUMBER(MATCH(Dates,$D$1:D$1,0)),"",Dates))),"")

In D2 control+shift+enter and copy down:

=IFERROR(INDEX(Tests,SMALL(IF(FREQUENCY(IF(1-(Tests=""),
    MATCH(Tests,Tests,0)),Ivec),Ivec),ROWS($D$2:$D2))),"")

In E2 just enter, copy across, and down:

=IFERROR(1/(1/COUNTIFS(Tests,$D2,Dates,E$1)),"")

 

[asad]

I knew you have lot of magical formulas in your store.
Now I will copy these and learn how to use them.

 

[tygrrboi]

I'm always in awe of Aladin's formulas.

 

[Aladin Akyurek]

I knew you have lot of magical formulas in your store.
Now I will copy these and learn how to use them.

I'm always in awe of Aladin's formulas.

Thanks for the kind words.

 

[asad]

Sir Aladin,
I tried tried and tried. but cannot work out what the formula is actually doing. Why do we have Ivec and when I calculate it as formula, it just gives me answer 1 no matter where I put it. And the formula that is using Ivec in D2, I cannot work that out as well.
If you have spare time, would you mind explaining to me how are these working? I want to learn these so that I can use them in future.

Asad

 

[Aladin Akyurek]

Sir Aladin,
I tried tried and tried. but cannot work out what the formula is actually doing. Why do we have Ivec and when I calculate it as formula, it just gives me answer 1 no matter where I put it. And the formula that is using Ivec in D2, I cannot work that out as well.
If you have spare time, would you mind explaining to me how are these working? I want to learn these so that I can use them in future.

Asad

The following link describes a FREQUENY formula which does unique counting: http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949. A formula for unique item listing is an expansion of the foregoing.

The following focuses on the FREQUENCY function itself: http://www.mrexcel.com/forum/excel-questions/818547-match-numbers.html#post3996274