Basically I have some data which is organised like this :

--------------------------------------------------------------------------
Date | Q1 | Q2 | Q3 | Q4 | Q5 |
--------------------------------------------------------------------------
08/18 | Yes | No | Yes | No | Yes |
--------------------------------------------------------------------------
07/18 | No | Yes | No | No | No |
--------------------------------------------------------------------------
07/18 | Yes | Yes | Yes | No | Yes |
--------------------------------------------------------------------------
08/18 | Yes | No | No | Yes | No |
--------------------------------------------------------------------------


I'd like to use maybe pivot table or something else to display the information like this :

-------------------------------------------------------------------
| Q1 | Q2 | Q3 | Q4 | Q5 |
--------------------------------------------------------------------------
Yes | 3 | 2 | 2 | 1 | 2 |
--------------------------------------------------------------------------
No | 1 | 2 | 2 | 3 | 2 |
--------------------------------------------------------------------------

and use the date a sorting mechanism to dynamically change the output

How can I achieve this? Any tips or ideas?

To make an example, I put the input data in A1:F5.

The result data will be in A8:F10. I filled in A9:A10 with Yes & No; B8:F8 with the Qs.

In B9, I put
=COUNTIF(B$2:B$5,$A9)
and copied it into B9:F10.

So far, that got the result specified in the post.

Then I tried to understand what "use the date a sorting mechanism" means here. Sorting A2:F5 by date leaves the result unchanged, which seems reasonable, but I suspect something more is intended here by the mechanism.

For calculating the results this works well thank you.

For the date I was looking for a way to have a field/button that could help me display either : All date, 07/18 or 08/18 (since these are the dates I have for now, but will eventually have more to account for).

This way I can generate monthly reports or annual ones.



On Friday, August 31, 2018 at 7:43:39 PM UTC-4, zvkmpw wrote:
To make an example, I put the input data in A1:F5.

The result data will be in A8:F10. I filled in A9:A10 with Yes & No; B8:F8 with the Qs.

In B9, I put
=COUNTIF(B$2:B$5,$A9)
and copied it into B9:F10.

So far, that got the result specified in the post.

Then I tried to understand what "use the date a sorting mechanism" means here. Sorting A2:F5 by date leaves the result unchanged, which seems reasonable, but I suspect something more is intended here by the mechanism.

I was looking for a way to have a field/button that could help me
display either : All date, 07/18 or 08/18

Here's one way, starting with the input data and result data arranged as before.

Step 1: Set up filtering by selecting A1:A5 and using
Home Editing Sort & Filter Filter
(I use Excel 2010; other versions might vary.)
This enables either viewing all rows or selecting rows by date.

Step 2: In G2, enter
=SUBTOTAL(103, A2)
and copy down to G5. This returns 1 for visible rows; 0 for hidden rows.

Step 3: In B9, I put
=COUNTIFS(B$2:B$5, $A9, $G$2:$G$5, 1)
and copy it into B9:F10. This limits the counting to visible rows only.