COUNTIF Formula help

[TheyCallMeIke]

Hello everybody,

I need some help trying to get this formula to countif, but containing multiple values.

I know its sloppy but my current formula is (and it works):

=COUNTIF(February!K3:K9994,"3")+COUNTIF(February!K3:K9994,"5")+COUNTIF(February!K3:K9994,"9")+COUNTIF(February!K3:K9994,"12")+COUNTIF(February!K3:K9994,"37")+COUNTIF(February!K3:K9994,"39")+COUNTIF(February!K3:K9994,"46")+COUNTIF(February!K3:K9994,"47")+COUNTIF(February!K3:K9994,"54")+COUNTIF(February!K3:K9994,"73")+COUNTIF(February!K3:K9994,"74")+COUNTIF(February!K3:K9994,"75")+COUNTIF(February!K3:K9994,"76")+COUNTIF(February!K3:K9994,"77")+COUNTIF(February!K3:K9994,"78")

So what that is doing is telling the cell to add up everything on the February sheet in the K column containing the values 3, 9, 5, 12, etc. and add them all together. Again, i'm a newb and it's sloppy, but it works.

Now what I need to do, is not only have it add all of those up like above, but somehow add in for the formula to ALSO only countif cell D range or column D is populated with "HMO"

the ending result is that the specific cell with this formula will countif all of the rows containing those numbers in the K column listed above, but also only if they contain the text HMO in column D. so it has to meet both criteria.

Can anyone help me with this? If my explanation isnt clear (i'm terrible at explaining things) just let me know and i'll try to do better.

thanks so much for anyone that can help me with this.

 

[T. Valko]

Hello everybody,

I need some help trying to get this formula to countif, but containing multiple values.

I know its sloppy but my current formula is (and it works):



So what that is doing is telling the cell to add up everything on the February sheet in the K column containing the values 3, 9, 5, 12, etc. and add them all together. Again, i'm a newb and it's sloppy, but it works.

Now what I need to do, is not only have it add all of those up like above, but somehow add in for the formula to ALSO only countif cell D range or column D is populated with "HMO"

the ending result is that the specific cell with this formula will countif all of the rows containing those numbers in the K column listed above, but also only if they contain the text HMO in column D. so it has to meet both criteria.

Can anyone help me with this? If my explanation isnt clear (i'm terrible at explaining things) just let me know and i'll try to do better.

thanks so much for anyone that can help me with this.

Use cells to hold all those criteria:

Book1
	A	B
2	3	HMO
3	5	_
4	9	_
5	12	_
6	37	_
7	39	_
8	46	_
9	47	_
10	54	_
11	73	_
12	74	_
13	75	_
14	76	_
15	77	_
16	78	_
Sheet1

Then the formula is:

=SUMPRODUCT(--(ISNUMBER(MATCH(February!K3:K9994,A2:A16,0))),--(February!D3:D9994=B2))

 

[jeffreybrown]

How about...

=SUMPRODUCT(--(February!D3:D9994="HMO"),--(ISNUMBER(MATCH(February!K3:K9994,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

 

[barry houdini]

To include the extra condition you can use COUNTIFS (with an "S") if you have Excel 2007 or later, i.e.

=SUM(COUNTIFS(February!K3:K9994,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},February!D3:D9994,"HMO"))

 

[Fazza]

Perhaps instead with a pivot table?

 

[hiker95]

TheyCallMeIke,

How about:

Excel Workbook
	K	L	M
3	3		16
4	5
5	9
6	12
7	37
8	39
9	46
10	47
11	54
12	73
13	74
14	75
15	76
16	77
17	78
18
19
20
21	3
22
23
February

 

[p45cal]

Beaten to it, normally entered:

=SUMPRODUCT((February!K3:K9994={3,5,9,12,37,39,46,47,54,73,74,75,76,77,78})*(February!D3:D9994="HMO"))

 

[TheyCallMeIke]

How about...

=SUMPRODUCT(--(February!D3:D9994="HMO"),--(ISNUMBER(MATCH(February!K3:K9994,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

This one worked perfectly.

The others may have as well I just went down the line in order!

THANK YOU ALL SO MUCH, you are amazing people.

Always so helpful. I'd buy you all a beer if I could!!

Cheers!!