Level 1

8 points

Building a Dashboard

Hello and Thank you for helping me with this project, so basically, I'm building a "Dashboard" to see all filter data in one place, based on dates, amounts, categories, etc.

This is pulling all the info from another tab, so I have a category name: "Status" (with WIN/LOSS Categories) I would like to pull the "WIN" but only by year and month, from the first table that controls everything, so if I change the month date from march to april and year 2023 to 2022, the amount will automaticaly will work, but for the secon table will not work because this is the formula that I have,

COUNTIF(Status,"WIN")

COUNTIF(Status,"LOSS")

indeed, it gives me the amount but, not by mounth or year, aslo, for the third, table "Aveg Hold Time" need another formula to pull the "aveg hold time:

AVERAGE(Avg Time) and it's working but not by month or year.

This is the first part of many issues I have lol. Thank you for all your help, building my Dashboard.

MacBook Pro 16″, macOS 13.3

Posted on Apr 30, 2023 5:15 PM

Top-ranking reply

Badunit

Level 7

30,732 points

Posted on Apr 30, 2023 7:52 PM

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

View in context

20 replies

Badunit

Level 7

30,732 points

May 5, 2023 12:56 PM in response to RaulJ

Again, be more careful. You have all the knowledge to do this and a perfect example to work from but you are jumbling it up. Go through each parameter in the function one-by-one and the errors will be obvious.

Your formula:

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

How it is supposed to be:

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

Sum-values is correct as Reference::E
The first set of test-values is Reference::F. This is correct.
The first condition is ">="&Control Panel::$D$2. This is not the correct condition for Reference::F. You want to sum up the rows where Reference::F is "pegasus" (the value in A2), not ">= May 1, 2023". The correct condition is A2. You have an A2 in your formula but not in the correct place.
You are missing the test-values for the two date conditions. You have the conditions but not the values to be tested. You want to be testing Reference::A (both times).

RaulJ Author

Level 1

8 points

May 5, 2023 1:30 PM in response to Badunit

I was able to get the total sum but not by month and system

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

I think the issue is the dates and month, I'm not familiar with this. can you help me?

Badunit

Level 7

30,732 points

May 1, 2023 12:22 PM in response to RaulJ

The formula is not what I posted. You can copy/paste it from my last post.

RaulJ Author

Level 1

8 points

May 1, 2023 12:30 PM in response to Badunit

That's what I did and it's giving me that error, maybe, I'm doing something wrong, this is what is pasting in Apple numbers, maybe if I do it manually.

RaulJ Author

Level 1

8 points

May 1, 2023 3:12 PM in response to Badunit

Great, can you help me with the formulas? I don't have a lot of knowledge of formulas; I was trying to do it and is not working, I think is the correct formula, would you help me with this one?

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Building a Dashboard