Count & What IF Statement

[rhjake23]

Hello.

I am trying to do the following.

If range A1:A1000 = "ABC" then count range B1:B1000

FYI there are formulas in range B1:B1000
so when in Excel and I highlight B1:B1000 i get a count of 1000..not sure why ....maybe because each cell has a formula ?
Please Help !! Thank you.

 

[Michael M]

Book1
	A	B	C	D	E
7					5
8	a		2
9	a		3
10	a		4
11	a		5
12	a		35
13	b		32
14	b		3
15	b		4
16	b		5
17	c		6
18	c		4
19	c		3
20	c		3
21	c		2
Sheet1

Cell Formulas
Range	Formula
E7	E7	=COUNTIF(A8:A21,"a")

 

[rhjake23]

Sorry for all the confusion.
please see attached
Thank you.

 

[Fluff]

How about

=COUNTIFS(D:D,"ABC",E:E,">0")

 

[rhjake23]

Thanks.
I get 0 as a result. Is that because I have formulas in Column E to get a value in the first place?

 

[Fluff]

A formula in col E shouldn't make any difference

+Fluff v2.xlsm
	C	D	E	F	G
5
6	117	ABC			4
7	175	ABC			6
8	286	ABC	2.86
9	253	ABC
10	119	ABC
11	220	ABC	2.2
12	159	ABC
13	200	ABC	2
14	255	ABC
15	112	ABC	1.12
16	222	XYZ	2.22
17	253	XYZ
18	153	XYZ
19	237	XYZ
20	220	XYZ	2.2
21
Data

Cell Formulas
Range	Formula
C6:C20	C6	=RANDARRAY(15,,100,300,1)
G6	G6	=COUNTIFS(D:D,"ABC",E:E,">0")
G7	G7	=COUNT(E6:E20)
E6:E20	E6	=IF(ISEVEN(C6),C6/100,"")
Dynamic array formulas.

What do you get if you use the formula i've got in G7?

 

[rhjake23]

Hi. I copied the values in Column E to another cell off to the right.... to get an integer ... the formula you previously gave me now works!
Probably an extra step..but I'm good with that....
Can I do a countif (a1:a10),"TEST",sum (b1:b10).... ? not sure the correct syntax..
ie...I want to get a sum of the values now...vs. the actual count.
THANK YOU!

 

[Fluff]

How about

+Fluff v2.xlsm
	C	D	E	F	G
5
6	128	ABC	1.28		6.36
7	143	ABC
8	159	ABC
9	155	ABC
10	296	ABC	2.96
11	135	ABC
12	103	ABC
13	212	ABC	2.12
14	289	ABC
15	217	ABC
16	205	XYZ
17	260	XYZ	2.6
18	118	XYZ	1.18
19	280	XYZ	2.8
20	189	XYZ
Data

Cell Formulas
Range	Formula
G6	G6	=SUMIFS(E:E,D:D,"ABC",E:E,">0")
E6:E20	E6	=IF(ISEVEN(C6),C6/100,"")

 

[rhjake23]

THATS AWESOME ! THANK YOU !

 

[Fluff]

Glad to help & thanks for the feedback.