CODEWITHSUNDEEP
excelexcel-2010
Anthony Olivieri
Anthony Olivieri

Reputation: 15

Count a specific value disregarding repeating dates

First time post, lots of help over the years though--I've seen some articles dealing with my problem but am having a block for some reason & thought it best to just ask...

I have a list of students in column A, classes, such as Math or English in column B, & the dates of said classes in column C.

I'm looking for a formula that will count all the classes that took place for a specific subject. So if there are 10 names in column A & the same date for all 10 in column C, it's counting it as 10 separate classes, rather than 1 class for 10 people.

Any help is greatly appreciated!

Upvotes: 1

Views: 46

Answers (2)

pnuts
pnuts

Reputation: 59485

In my opinion easier, and very much more versatile, would be to use a PivotTable. This would allow a lot of additional analysis, such as by week/month/year or by Student, etc and can show the results for all classes at one time without a change of parameter:

enter image description here

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

Try this:

=SUM(IF("subject"=$B$2:$B$99,1/(COUNTIFS($B$2:$B$99,"subject",$C$2:$C$99,$C$2:$C$99)),))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Note: replace "subject" with a reference to the particular subject you wish to count distinct class dates for.

Note: replace the 99s with a row number deep enough to accommodate your data.

Note: the formula assumes you have column headers in the first row and that the data begin on the 2nd row.

Upvotes: 1

Related Questions