Formulas for Dashboard to calculate "ALL"

[Freeman2022]

I'm using excel 365

I’m in the process of making a dashboard..

I would like to calculate the total cases by Year and by Customer Name

I will use combo boxes for both, or maybe radio buttons for the year.

I’m having trouble with the formulas because of the “ALL” Years and “ALL” Customers.

Any suggestions???

 

[FormR]

Hi, you could try something like this.

Book1
	A	B	C	D	E	F
1	Year	Customer	Cases		Year drop down >	All Years
2	2022	A	DFSFS		customer drop down >	All Customers
3	2019	B	DFD		Formula >	5
4	2022	D	D
5	2022	E	D
6	2012	A	SA
Sheet1

Cell Formulas
Range	Formula
F3	F3	=COUNTIFS(A:A,IF(F1="All Years",">0",F1),B:B,IF(F2="All Customers","*",F2))

 

[Freeman2022]

Hi, you could try something like this.

Book1
	A	B	C	D	E	F
1	Year	Customer	Cases		Year drop down >	All Years
2	2022	A	DFSFS		customer drop down >	All Customers
3	2019	B	DFD		Formula >	5
4	2022	D	D
5	2022	E	D
6	2012	A	SA
Sheet1

Cell Formulas
Range	Formula
F3	F3	=COUNTIFS(A:A,IF(F1="All Years",">0",F1),B:B,IF(F2="All Customers","*",F2))

THANK YOU!!!!! That works!!!!!

 

[Freeman2022]

Hi, you could try something like this.

Book1
	A	B	C	D	E	F
1	Year	Customer	Cases		Year drop down >	All Years
2	2022	A	DFSFS		customer drop down >	All Customers
3	2019	B	DFD		Formula >	5
4	2022	D	D
5	2022	E	D
6	2012	A	SA
Sheet1

Cell Formulas
Range	Formula
F3	F3	=COUNTIFS(A:A,IF(F1="All Years",">0",F1),B:B,IF(F2="All Customers","*",F2))

Ok wait, that works if I select ALL, now how would I incorporate that into if I only selected 2019? or 2020?

 

[FormR]

now how would I incorporate that into if I only selected 2019? or 2020?

Hi, works as is for me - does it not for you?

Perhaps some sample data that demonstrates the problem will help.

Book1
	A	B	C	D	E	F
1	Year	Customer	Cases		Year drop down >	2022
2	2022	A	DFSFS		customer drop down >	All Customers
3	2019	B	DFD		Formula >	3
4	2022	D	D
5	2022	E	D
6	2012	A	SA
Sheet1

Cell Formulas
Range	Formula
F3	F3	=COUNTIFS(A:A,IF(F1="All Years",">0",F1),B:B,IF(F2="All Customers","*",F2))

 

[Freeman2022]

Hi, works as is for me - does it not for you?

Perhaps some sample data that demonstrates the problem will help.

Book1
	A	B	C	D	E	F
1	Year	Customer	Cases		Year drop down >	2022
2	2022	A	DFSFS		customer drop down >	All Customers
3	2019	B	DFD		Formula >	3
4	2022	D	D
5	2022	E	D
6	2012	A	SA
Sheet1

Cell Formulas
Range	Formula
F3	F3	=COUNTIFS(A:A,IF(F1="All Years",">0",F1),B:B,IF(F2="All Customers","*",F2))

Hi thank you for responding, yes your formula works in that format, however, see columns I-M, what formula would I use in columns J and M, I would like to build a chart from those areas which is why I would need that format. See additional attachment

 

[FormR]

what formula would I use in columns J and M

Perhaps you can give the expected results for a couple of different scenarios of what should be returned in those columns when different items are selected in the drop down?

 

[Freeman2022]

Perhaps you can give the expected results for a couple of different scenarios of what should be returned in those columns when different items are selected in the drop down?

See column N,
if I select ALL Years then total cases in columns J and M should return
If I select Year 2019 then total cases in columns J and M should return
If I select Year 2021 and Customer D then total cases in columns J and M should return

 

[FormR]

If i've understood correctly - you could try this in J2 copied down:

=IF($E$19="All Years",COUNTIFS(A:A,I2),IF(I2=$E$19,COUNTIFS(A:A,I2),0))

And similar for M2.