6

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.

5
+100

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. ;)

  • That's kinda what I'm looking to do. This is the best way I can describe it using the code you gave. =AND(January::$A:$AE=$A2, January::$A2=$B1) or in pseudo code foreach (January::$A:$AE as #item) { if (#item == $A2 AND January::$A2 == $B1) – Mark Tomlin Dec 17 '13 at 6:12
  • I want it to COUNTIF January::$A:$AE is equal too $A2 AND January::$A2 is equal too $B1. Basiclly I want to only count it, if two conditions are met. – Mark Tomlin Dec 17 '13 at 8:31
  • I see what you're trying to do now. While I can think of several ways to do it, they're fairly complex. How badly do you want to keep it in Numbers? This would be pretty easy in R (free statistical analysis software) if you're willing to use that. (It works on a lot of platforms, not just the Mac). – Justin Mrkva Dec 17 '13 at 18:19
  • If you don't mind providing the answer for numbers first, then for future projects I can move over to R. The reason I'm using numbers is because it directly correlates with the data I'm getting from the staff here who input it into an excel spreadsheet and then I import it into numbers and add some context (like day of week & day of month) and also do the pretty graph stuff for our call volume. – Mark Tomlin Dec 18 '13 at 12:44
  • Well, the simplest of my methods is to create a single "Days" table (what I call a "Fudge Table") and repeat the days. Then, you can use COUNTIFS because the dimensions of the two data sets are the same. Unfortunately that method is EXTREMELY slow; Numbers has a slight inefficiency (by slight I mean really severe) that makes it take several seconds to recalculate even on a fast machine. The second is to create seven subtables and manually pull out columns, avoiding using COUNTIFS. I'll attach examples of both in a few minutes. – Justin Mrkva Dec 18 '13 at 15:43
0

I'm not quite sure I understand what you're asking, but COUNTIFS should do what you want, allowing you to combine multiple COUNTIF formulas into a single cell:

COUNTIFS(test-values,condition,test-values,condition,…)

For instance, the formula for the Result cell in the below example is:

COUNTIFS(Data::A1:A8,">2",Data::B1:B8,">2")

There's no problem in using the same value for test-values and use a different condition each time — I think this is what you want.

You must log in to answer this question.

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