Nested?, Countif function or sumproduct formula, please need a tip

[david gonzalez]

Hi, this david

I have a question,
I'm using 3 Column (each one have hundreds of Rows) :
1-. Column "G"
2-. Column "L"
3-. Column "J"

Then,
-.In "Column G" I have a sets of letter --> C,E,G,I,K,M,O,Q,S,U,W
-.In "Column L" I have 27 Numbers -------> 3 to 30
-.In "Column J" I have 6 Numbers -------> 7,8,9,10


I need to Use a Formula to count each number Occurrences of
#7 from "Column J" with #3 of "Column L" + "Column G" with Letter C"
#8 from "Column J" with #3 of "Column L" + "Column G" with Letter C"
#9 from "Column J" with #3 of "Column L" + "Column G" with Letter C"
#10 from "Column J" with #3 of "Column L" + "Column G" with Letter C"
Then the same "Column J" with 4,5,6,7...etc of "Column L" + "Column G", "E", then with "I", "K", "M" etc,etc,etc

I Used COUNTIF Formula and SUMPRODUCT formula, I don't really know where can be the problem..

Please Login or Register  to view this content.

Please any help?


Best Regards

David

[FDibbins]

Untested because of no data, but try this...
=countifs(J:J,7,L:L,3,G:G,"C")

[david gonzalez]

HI,

Thanks Mr FDibbins,

I Inserted the formula starting in Row I5 and didn't work,
it count O occurrences.

Please Login or Register  to view this content.

Please, sample sheet is attached





Best Regards

David

[david gonzalez]

Hi, !!

I make sure that column "L" and "J" are formatted as number (they are)
Obviously i'm doing something wrong, but i don't know where..

Then i tried different formulas (different way) Including as Absolute referent, array, and didn't worked.

This are the formulas that i tried :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks


Best Regards

David

[FDibbins]

Its giving 0 because there are no rows where all 3 conditions are met

To test, apply filters, then filter on 7 in J

[david gonzalez]

Hi Mr FDibbins,

i think it should have occurrences of at least Value 1 (because condition happened at least 1)

I inserted a new order Formula and it show occurrences of 1 and some 0's
I don't know if is the right one,
(i have to verify if manually if correct value, lol )

Please Login or Register  to view this content.

Thanks


Best Regards

David

[FDibbins]

The formula works fine, you just dont have any rows that have that combination inthem. Below is a filtered version, filtered on 7...

	F	G	H	I	J	K	L	M	N
2		LETTERS C to W		Occurrences	Numbers 7 to 10		Numbers 3 to 30
3	1-Apr	W			7		22		0
4	4-Apr	M			7		12
5	5-Apr	E			7		8
6	8-Apr	S			7		14
7	8-Apr	S			7		15
8	9-Apr	M			7		26
9	12-Apr	M			7		23
10	12-Apr	M			7		24
11	13-Apr	M			7		21
12	13-Apr	M			7		22
13	16-Apr	S			7		15
14	18-Apr	W			7		8
15	18-Apr	S			7		20
16	18-Apr	K			7		8
17	18-Apr	K			7		9
18	18-Apr	K			7		10
19	9-Apr	M			7		26
20	12-Apr	M			7		23
21	12-Apr	M			7		24
22	13-Apr	M			7		21
23	13-Apr	M			7		22
24	16-Apr	S			7		15
25	18-Apr	W			7		8
26	18-Apr	S			7		20
27	18-Apr	K			7		8
28	18-Apr	K			7		9
29	18-Apr	K			7		10

[FDibbins]

If I change a row to show C and 3, then it counts 1

	F	G	H	I	J	K	L	M	N
2		LETTERS C to W		Occurrences	Numbers 7 to 10		Numbers 3 to 30
3	1-Apr	C			7		3		1
4	4-Apr	M			7		12
5	5-Apr	E			7		8
6	8-Apr	S			7		14
7	8-Apr	S			7		15
8	9-Apr	M			7		26
9	12-Apr	M			7		23
10	12-Apr	M			7		24
11	13-Apr	M			7		21
12	13-Apr	M			7		22
13	16-Apr	S			7		15
14	18-Apr	W			7		8
15	18-Apr	S			7		20
16	18-Apr	K			7		8
17	18-Apr	K			7		9
18	18-Apr	K			7		10
19	9-Apr	M			7		26
20	12-Apr	M			7		23
21	12-Apr	M			7		24
22	13-Apr	M			7		21
23	13-Apr	M			7		22
24	16-Apr	S			7		15
25	18-Apr	W			7		8
26	18-Apr	S			7		20
27	18-Apr	K			7		8
28	18-Apr	K			7		9
29	18-Apr	K			7		10

[david gonzalez]

Hi Mr FDbbins

now I Understand... due to The Formula (

Please Login or Register  to view this content.

It work Only for criteria "C" "7" and "3",

I tough that with same formula it might recognize whatever 3 criteria are repeated with in range.
(i'm right or i'm lost again??.. lol )

It's means that this formula it just work for exact "G" "7" and "3",
if i need to count "W" "9" "22" then i have to adjust the formula with that criteria to be count in the whole range..


Ok, I'm sorry to bother that much..


Thank You !!!!


Best Regards

David

[FDibbins]

You are not bothering at all

It work Only for criteria "C" "7" and "3"

Yes, because that was hard-coded into the forumla.

I think what you need to do is make up a small table, like this...

	N	O	P
4	Letter	Numbers 7 to 10	Numbers 3 to 30
5	W	7	3
6
7	0

Then N7=COUNTIFS(J:J,$O$5,L:L,$P$5,G:G,$N$5)

Now, you can put whatever you want counted in there

[david gonzalez]

Hi Mr FDbbins,

Indeed, we (the new bee) must listen when experience talk..

Yes ! it was a great idea to make another table, it work perfect !!

I tough about that, but didn't imagine to do it with an small table,

I Imagined to do it in many cells to use as a references criteria and hide these cells (huge work ! )


Thank you so much !!!


Best Regards

David

[FDibbins]

It was a pleasure to help you and thanks for the feedback call again soon with your next question