Can I use a Dropdown box with a COUNTIF formula?

[Muller]

Hello folks,


I am trying to write a formula where if I select a value from a dropdown the numbers that are entered below will be counted or not counted, depending on the option selected.

In my drop-down menu, I have two options: A or F. I want it to work so that the row below cells where the drop downs are only active and count the numbers dependant on what option is selected

For Example:

A1 to Z1 are dropdowns
A2 to Z2 are where the numbers below are entered

I only want to count the numbers entered into the sheet that have A selected from the drop-down list above the cell where the number is entered. So if there is a numerical entry in row A2 with an A selected from the drop down, I want it to be counted. If there is a numerical entry on Row A2 with an F above it, I don’t want the number counting.

I think I need a COUNTIF formula of some description, but I'm not sure how to write this. Any help I can get here would be greatly appreciated.

 

[etaf]

=countifs(A1:Z1,"A",A2:Z2,">"&0)
that will count the numeric values with an A in the dropdown

if i understand correctly
in the example 3 - as 1 value with an A is blank
but maybe it may have zero
=COUNTIFS(A1:Z1,"A",A2:Z2,"<>")

Book2
	A	B	C	D	E	F	G
1	a	f	a	f	f	a	a
2	12	123	234	345	456		678
3
4	count = a	3
Sheet1

Cell Formulas
Range	Formula
B4	B4	=COUNTIFS(A1:Z1,"A",A2:Z2,">"&0)

Book2
	A	B	C	D	E	F	G	H	I
1	a	f	a	f	f	a	a	a
2	12	123	234	345	456		678	0
3
4	count = a	4
5
Sheet1

Cell Formulas
Range	Formula
B4	B4	=COUNTIFS(A1:Z1,"A",A2:Z2,"<>")

 

[Muller]

Thank you very much. Your first example worded as intended. Much appreciated

 

[etaf]

you are welcome
note the 1st example will not count any entries with a zero

 

[Muller]

After ding some experimenting with the form I was working on using the solution provided that I said worked, it doesn't quite do what I need it to. So...I think I may have put a bad explanation in for what I wanted!

Here is an example of how I would like the sheet to work:

A	A	A	F	F	A	F	A	A	F		Total
1	4	2	5	7	3	2	6	4	6		20​

I need it to work so that if A is selected on the row above the numbers, then the value entered underneath the A gets counted in a total column at the end of the row.

Is this possible at all?

 

[etaf]

so not count, but sum the numbers
=SUMIF(A1:J1,"A",A2:J2)

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	A	A	F	F	A	F	A	A	F		Total
2	1	4	2	5	7	3	2	6	4	6		20
Sheet1

Cell Formulas
Range	Formula
L2	L2	=SUMIF(A1:J1,"A",A2:J2)

 

[Muller]

Thank you! I have not heard of SUMIF formulas before. I will give this a try jn the morning!

 

[Muller]

so not count, but sum the numbers
=SUMIF(A1:J1,"A",A2:J2)

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	A	A	F	F	A	F	A	A	F		Total
2	1	4	2	5	7	3	2	6	4	6		20
Sheet1

Cell Formulas
Range	Formula
L2	L2	=SUMIF(A1:J1,"A",A2:J2)

Worked perfectly! Thanks again!

 

[etaf]

you are welcome