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!!!

 

[Aladin Akyurek]

=COUNTIFS(A:A,"?*",B:B,$E1)

where E1 houses a date of interest like 16-May-2016.

 

[Confusedgirl]

=COUNTIFS(A:A,"?*",B:B,$E1)

D2 is the name of the test eg. FST_NANA.

 

[Aladin Akyurek]

You have:

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

A​

[/td][td]

B​

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

1​

[/td][td]Tests[/td][td]EarlyStart[/td][/tr]


[tr][td]

2​

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

17-May-16​

[/td][/tr]


[tr][td]

3​

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

17-May-16​

[/td][/tr]


[tr][td]

4​

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

18-May-16​

[/td][/tr]


[tr][td]

5​

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

18-May-16​

[/td][/tr]


[tr][td]

6​

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

16-May-16​

[/td][/tr]


[tr][td]

7​

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

16-May-16​

[/td][/tr]


[tr][td]

8​

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

16-May-16​

[/td][/tr]


[tr][td]

9​

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

16-May-16​

[/td][/tr]


[tr][td]

10​

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

16-May-16​

[/td][/tr]


[tr][td]

11​

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

18-May-16​

[/td][/tr]


[tr][td]

12​

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

16-May-16​

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

From your description, it looked like you wanted to know how many tests are done on a given day (date)... If this is not what you are after, would you care to specify what is wanted by way of an example based on the ranges shown in the above exhibit?

 

[Confusedgirl]

Hi,

That's exactly what I'm after.

I just wasn't sure what the "?*" means in the formula you suggested. Do i simply add those characters to the formula?

 

[Aladin Akyurek]

Hi,

That's exactly what I'm after.

I just wasn't sure what the "?*" means in the formula you suggested. Do i simply add those characters to the formula?

I'll come back to that...

Given the exhibit, what is the count you want for 16-May-2016?

 

[Confusedgirl]

I would like the number of each test per day. So for the 16/05/16 the count would be:
ZFLOW20 - 1
FST_NANA - 2
POOL - 3


For the 17/05/16
FST_NANA - 1
SWCPITDIS - 1

etc...

 

[Aladin Akyurek]

I would like the number of each test per day. So for the 16/05/16 the count would be:
ZFLOW20 - 1
FST_NANA - 2
POOL - 3


For the 17/05/16
FST_NANA - 1
SWCPITDIS - 1

etc...

Are you trying to count:

1. tests on a given day say 16-May-2016?

2. (different) days a test say FST_NANA has been run?

Which one is it if any?

 

[Confusedgirl]

The first option. The number of tests on a given day but broken down to the different types of tests.

 

[Aladin Akyurek]

The first option. The number of tests on a given day but broken down to the different types of tests.

You might want to run a pivot table: Select the data area including the headers and then run INSERT | PivotTable from the ribbon...

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

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

15​

[/td][td]Count of Tests[/td][td]Column Labels[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

16​

[/td][td]Row Labels[/td][td]

16-May-16​

[/td][td]

17-May-16​

[/td][td]

18-May-16​

[/td][td]Grand Total[/td][/tr]


[tr][td]

17​

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

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

4​

[/td][/tr]


[tr][td]

18​

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

3​

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

3​

[/td][/tr]


[tr][td]

19​

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

1​

[/td][td]

1​

[/td][/tr]


[tr][td]

20​

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

1​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]


[tr][td]

21​

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

1​

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

1​

[/td][/tr]


[tr][td]

22​

[/td][td]Grand Total[/td][td]

6​

[/td][td]

2​

[/td][td]

3​

[/td][td]

11​

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