SUM CountIFS

[dragonmouse]

Simple question for the Pro's.

I have two columns A and B. Both Columns may contain Letters A-D. I want to count all the "A" In Column A and all the "A" in column B then add them together.

I tried using =COUNTIFS($A:$A,"A",$B:$B,"A") however it would only count one column. I tried using =SUM(COUNTIFS($A:$A,"A",$B:$B,"A")) but it's still only counting one column.

Priority (column A)	New Pri (Column B)	"A" PRI Count (formula)
A	B	1​
A	A	2​
B	A	1​
C	A	1​
D	A	1​
A	A	2​
B	C
B	C
B	D
C	A	1​

 

[Fluff]

How about

=COUNTIFS(A:B,"A")

 

[dragonmouse]

How about=COUNTIFS(A:B,"A")

Ok...yes that works but not quite what I was looking for. My bad in describing my problem. I should have separated the columns more. In "reality" there is a column between the two that would NOT be counted. This formula adds columns A and B fine but If I have columns separated sucs as A and C it will add all THREE columns. I apologize but I didn't consider that in my example.

Priority (column A)	Nothing of interest (Column B)	New Pri (Column C)	"A" PRI Count (formula) (Column D)
A	1	B	1
A	2	A	2
B	D2	A	1
C	A2	A	1
D	5	A	1
A	7	A	2
B	D1	C
B	A1	C
B	B3	D
C	A7	A	1

 

[Fluff]

Could you have an A on it's own in col B?

 

[dragonmouse]

Could you have an A on it's own in col B?

It could be a possibility but I wouldn't want to count it.

I would need something that would "count any occurence of the letter "A" in column A, count any occurence of the letter "A" in column C THEN add them together".

 

[hrayani]

TEST FILE.xlsm
	A	B	C	D
1	Priority (column A)	Nothing of interest (Column B)	New Pri (Column C)	"A" PRI Count (formula) (Column D)
2	A	1	B	1
3	A	2	A	2
4	B	D2	A	1
5	C	A2	A	1
6	D	5	A	1
7	A	7	A	2
8	B	D1	C	0
9	B	A1	C	0
10	B	B3	D	0
11	C	A7	A	1
Sheet4

Cell Formulas
Range	Formula
D2:D11	D2	=IF(A2="A",1,0)+IF(C2="A",1,0)

 

[Fluff]

How about

=SUMPRODUCT((A2:A100="A")+(C2:C100="A"))

 

[dragonmouse]

How about=SUMPRODUCT((A2:A100="A")+(C2:C100="A"))

BINGO. That is the answer. Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dragonmouse]

=IF(A2="A",1,0)+IF(C2="A",1,0)

This did not work=IF(A2="A"...) gave me an error that it didn't recognize it as a formula

=SUMPRODUCT(($A:$A="A"))+($C:$C="A")) worked. A slight modification to "Fluff's" formula but the essence worked