Summarizing data in excel

Question

Im trying to summarise some data that i have in a spreadsheet. See below for an extract. Basically, im trying to count how many "Yes"&"N/A" there are for each month and also how many "No" there are for each month and display it in the form of a chart.

I've managed to do it as an overall but cant seem to break it down per month.

Any help would be appreciated. thanks!

Overall Performance Chart and Counts

Larger Extract of Data

Answer 1

I'm not sure what your setup looks like, but this works for me.

In A1:B14 January Yes January Yes January Yes February Yes March Yes April No May No June No July No August Yes September Yes October Yes November Yes December Yes

In F1:F12 January February March April May June July August September October November December

G1 = Yes H1 = No

I1 = =SUMPRODUCT((A:A=F1)(B:B=$G$1)) J1 = =SUMPRODUCT((A:A=F1)(B:B=$H$1))

Fill down.

Answer 2

I'm not entirely clear on how your data are structured - it would have helped to see more than one row - but I think you're looking for countifs. It's just like countif, except you can define multiple criteria for it to use when counting. If you construct a table that captures all of your conditions (i.e. each month and all of the possible answers), then you can write a countifs statement to count the number of occurrences of each combination in your data.

Example on made-up data

In my example, the formula in the top left cell of the summary table is =COUNTIFS($A$2:$A$18,$D5,$B$2:$B$18,E$4), which you can drag to the rest of the table.