0

Consider the following table which shows a generalised, abstract version of my specific real-world problem (which I will also illustrate).

enter image description here

Each "1" or "0" is part of a column numbered I..III, which in turn belongs to one of the, let's call them "super-columns" ("x..z"). This means that, for each case, each super-column will contain a "1" (or a "0") n times. For example, in case1, the super-column "x" contains a "1" once.

Now I need to calculate a specific value: the number of times a "1" (or a "0") appears in any super-column a specific number of times. I need to be able, for example, to answer the question how often does a "1" appear as part of a super-column more than twice? ...for case1, the answer would be "2", as super-columns "y" and "z" contain a "1" two, or three times, respectively.

My intuition is that a combination of COUNTIFS and SUMPRODUCT might work, but my own attempts have been so unsuccessful they're not even worth describing them here.

For the sake of completeness, this is the background, or the practical application that I have in mind:

enter image description here

A digital attendance list for classes that I teach. Note the "super-columns" again that represent days of the week, and the "sub-columns" (1..8) for the up-to-eight classes that students are supposed to attend per day. "." = present, "x" = absent. My problem is to not only count the number of lessons a student has missed, but also the number of entire school days that they were absent (a day being counted as a day of absence if four or more lessons were missed). So for the two cases in this table, both students have (nominally) missed an entire school day.

Improve this question

2 Answers 2

Reset to default
0

Try the XMATCH function to give you a count of matching cells.

Modify it to your needs.

https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312

Improve this answer
2
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Sep 28, 2023 at 10:42
  • Your answer does not fit the Excel 2010 tag, this feature was introduced in Excel 2021. Yes, some people still use very old programs.
    – JohnSUN
    Commented Sep 28, 2023 at 19:52
0

You can sum up the 1's in auxiliary columns and then count them according to your needs.
Counting 1's

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .