COUNTIF using two conditions from data inside and outside the dataset

numbers

I'm struggling to use numbers to do a function. Specifically, I want to use COUNTIF to check two things within my data set table and put that information into another table that we'll call "query set". I want to check for one variable that is within the first column of the query set is the same as the first colum in the data set and that the value is the same as the query set's first header.

I just don't know how to do that because using COUNTIF("Raw::January::1st:31st", $A2) works for making sure that one query condition. I guess the question is how do I filter COUNTIF so that it uses the second query as a check as well? I kinda want to do something like this, want it to only COUNTIF(January::$A:$AE,$A2, January::$A:$AE,"$January::$A:$AE = B$1").

You can take a look at the spreadsheet here …

https://www.icloud.com/iw/#numbers/BALRr1zYI0yyan4pV9yBoRCulAUCthE6_niE/Ask_Different

So what I want to do is check that the value of the cell is equal to $A2, and that the header of that cell is the day of the week I'm looking for as well.

Best Answer

It's probably easiest to add another column with:

AND(A1=1,B1=1)

and then use

COUNTIF(Table 1::C,"=TRUE")

You can hide the extra column if you want by selecting the column's drop-down arrow and clicking "Hide Column".

Minimal example: https://www.icloud.com/iw/#numbers/BAJy4qiGzoTh1VSmyA-BZ16VCStKBAKb9pSE/TEST (link will be removed in 1 week)

PARTIAL SOLUTIONS:

Example using Fudge Tables: https://www.icloud.com/iw/#numbers/BAI89SfR5AMJFjId3zyBzaZuxCGzy9uGo5eE/Fudge_Tables.numbers

Example using Subtables: https://www.icloud.com/iw/#numbers/BAJMsEi-8NDWoPbLaxeBsLTzYZ2Mqnmqo5eE/Subtables

Note that Subtables has a problem with zeroes (0000 translates to a number in the subtable). The best solution (as I did) is to put something like an "X" in every space that doesn't have a number in it in the main table.

Of course, be sure to check the data. The biggest problem with these methods is that they're very manual labor intensive and are very error prone. I blazed through them pretty quickly so I easily could have made a mistake. Just be careful. ;)

Related Question