Building a Dashboard

COUNTIFS will let you specify multiple conditions.

Based on the formulas you posted, I assume you have the Numbers preference setting "use header names as labels" checked. It is easier to help when that is not checked and it uses the normal cell and range referencing.

If Status is Table 1::A and Date is Table 1::B and you want the wins for January 2023:

=COUNTIFS(Table 1::A,"win",Table 1::B,">="&DATE(2023,1,1),Table 1::B,"<"&DATE(2023,2,1))

Working with dates can get a little complicated/long. You don't want to calculate the same thing repeatedly if not necessary. You'll need the date several times so I recommend adding a new column (column D) to your Control Panel table for the date and use this formula in cell D2:

=DATE(B2,MONTH(A2),1)

That is the first of the month. You can hide this column later.

EOMONTH can give the last day of that same month.

The entire formula for the Win column (column A), with all the necessary preserving of column references so you can drag-fill it to column B is:

=COUNTIFS(Table 1::$B,A$1,Table 1::$A,">="&Control Panel::$D2,Table 1::$A,"<="&EOMONTH(Control Panel::$D2,0))

First, please turn off "use header names as reference".

Second, I apologize for not changing the name of my "Table 1" to "Reference", like in your spreadsheet.

Those two things plus it looks like you didn't recreate the formula accurately. You should be able to copy/paste the formula from here.

=COUNTIFS(Reference::$B,A$1,Reference::$A,">="&Control Panel::$D2,Reference::$A,"<="&EOMONTH(Control Panel::$D2,0))

Use AVERAGEIFS and SUMIFS, the ones with the S at the end of their name. They will be very similar to how you did the COUNTIFS formula. You can read about them in Help->Formulas and Functions Help. Half the battle is knowing what functions are available (like knowing there is a SUMIFS as well as SUMIF), the other is figuring out how to get them to work. You now know which functions to use. You have the working COUNTIFS as a guide to using them correctly. Give it another try.

The formulas for Avg hold time and #shares should be very similar in look to the COUNTIFS functions you just made, except using AVERAGEIFS and SUMIFS. If you want all of them to give consistent results (counting the same rows, averaging the same rows, and summing the same rows), they need to all use the same conditions.

One of your date conditions is missing. The condition was in the formula you posted prior to this. Now it is gone. Sometimes (far too often) Numbers, in its attempt to be helpful, can totally screw up a formula when you try to edit it. Maybe that's what happened?

The condition ">="&A2 means "A2 or any other text/word that would come after it in a dictionary". The condition you want is just plain old A2 (which is the same as "="&A2).

Here is the formula. Hopefully it is correct. I did not recreate your spreadsheet to test it.

=SUMIFS(Reference::E,Reference::F,A2,Reference::$A,">="&Control Panel::$D$2,Reference::$A,"<="&EOMONTH(Control Panel::$D$2,0))

You have added a new column to your Reference table that was not there when I wrote the formula or when I last said it could be copy/pasted from my post. You will have to edit the formula so it refers to the correct columns of your newly designed Reference table. Or put the Reference table back like it was until the problem at hand is fixed.

The A$1 you circled is the part that was incorrect before. It was just a 1 in your earlier screenshot. It should be a cell reference, though. I do not understand why it is not. It should be a reference to the cell directly above that has the word "Win" in it. Numbers does weird things sometimes. Select that A$1 in the formula then click on cell A1. That should delete the text A$1 and replace it with the cell reference. If that doesn't work, replace it with "win" in this formula and replace it with "lose" in the one next to it (like you had in your original formulas).

You can do this. Be more careful. Take another look at the description of the SUMIFS formula and take another look at your formulas.

SUMIFS(sum-values, test-values, condition, test-values, condition, and so on and so on)

Your formulas do not follow that pattern.

You formula for #shares appears to work. It uses the same SUMIFS function. Use it as your guide.

Specific to your formula for cell System::B2:

You are using cell Reference::F3 to get the word "Pegasus". You should be referencing System::A2.

Specific to your formula for cell Return::B2:

If it is supposed to be adding up the amounts from the prior month (a month prior to the one in Control Panel), I think the two conditions you want are

">"&EOMONTH(Control Panel::$D$2,-2)

and

"<="&EOMONTH(Control Panel::$D$2,-1)