Formula needed (cells containing specific texts/numbers)

[david_davidovich]

Hello all.

I have a huge excel sheet with several columns. Of those, two columns are important for me. Let’s call them “A” and “B” columns. “A” and “B” contain one of many text or number values (like 1 of 20). First, i need a formula which gives me "1" (true) if

- “A” column contains one of the several sought values --- let’s say we have a column with colors, and i need to filter for “red” OR “blue” OR “yellow”

AND

- “B” column contains one of several sought values --- in this, i have IDs which contain letters as well as digits, let’s say filter for “AAA000” and “AAA111”


If it’s false, I just need a 0 / false.

It is important for me to filter for the whole columns because the actual needed formula because
1. this database is in daily use so it gets new rows every day
2. at the end i need to summarize the number of cases (rows) where both column values proved to be true (1).

It would be best if i just got a final formula which gives me a single number of the summarized cases. But it's also good if you give me something with which i can create a new column either containing "1" or "0" for every row, and then i just summarize that column.

Thank you very much in advance.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula needed (cells containing specific texts/numbers)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Eric W]

Welcome to the MrExcel forum!

How about:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	A Column	B Column			Colors	Values
2	Pink	AAA000	0		Red	AAA000
3	Blue	AAA111	1		Blue	AAA111
4	Yellow	XYZ123	0		Yellow
5	Yellow	AAA000	1
6	Red	CCC333	0
7	Red	AAA000	1
8	Red	AAA111	1
9
10			4	4
Sheet11

Cell Formulas
Range	Formula
C2:C8	C2	=COUNTIF($E$2:$E$8,A2)*COUNTIF($F$2:$F$8,B2)
C10	C10	=SUM(C2:C8)
D10	D10	=SUMPRODUCT(--ISNUMBER(MATCH(A2:A8,E2:E8,0)),--ISNUMBER(MATCH(B2:B8,F2:F8,0)))

 

[david_davidovich]

Welcome to the MrExcel forum!

Thank you very much, this seems like a nice solution!

Can you tell me what D10 does exactly? I'm fairly new to Excel...

Also, do i understand correctly that by writing A2 and B2 at C2 formula, you are inspecting the whole column? So if i add a 9th row it would do automatically the same thing with "1" or "0".

 

[Eric W]

Also, do i understand correctly that by writing A2 and B2 at C2 formula, you are inspecting the whole column? So if i add a 9th row it would do automatically the same thing with "1" or "0".

Not exactly. The C2 formula looks to see if A2 is in the E2:E8 list, then it sees if B2 is in the F2:F8 list. Then if both things are true (we use the * as a Boolean AND operator), it creates the 1. So it doesn't look at the whole column. But I used the $ sign to signify absolute references on the E and F tables, and did not use a $ on the A2 and B2 values. So if you drag the formula down the column (or copy and paste it), the A2 changes to A3, and A3 to A4, and so on. So if you copy the formula to C9, it would adjust and look at the A9 value.

I just noticed you have Excel 365. In this case, you can change the C2 formula like so:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	A Column	B Column			Colors	Values
2	Pink	AAA000	0		Red	AAA000
3	Blue	AAA111	1		Blue	AAA111
4	Yellow	XYZ123	0		Yellow
5	Yellow	AAA000	1
6	Red	CCC333	0
7	Red	AAA000	1
8	Red	AAA111	1
9
10			4	4
Sheet11

Cell Formulas
Range	Formula
C2:C8	C2	=COUNTIF($E$2:$E$8,A2:A8)*COUNTIF($F$2:$F$8,B2:B8)
C10	C10	=SUM(C2#)
D10	D10	=SUM(COUNTIF($E$2:$E$8,A2:A8)*COUNTIF($F$2:$F$8,B2:B8))
Dynamic array formulas.

You put in the whole A2:A8 and B2:B8 range you want in the C2 formula, and the results will SPILL down the column. So you don't need to drag the formula down the column, just change the C2 formula if you add a row. In fact, with a little work, we can make it dynamic so you don't even need to update the formula at all. Also note that the C10 formula changes slightly.

As far as the original D10 formula, it looks at every row in A2:A8, sees if the value exists in E2:E8 using MATCH, and does the same thing for B2:B8. If both are true, it sets a 1 for that row, then sums up the whole array. But with 365, I changed it so it's merely a SUM of the C2 formula, which should be easier to understand.

Hope this helps!

 

[david_davidovich]

Thank you very much again!

I have only one more thing, could you give me the updated dynamic formula? Because i'm not the one that keeps updating the database, i'm only tasked with creating a formula / solution which solves the counting forever (so with all future rows). I would be much helpful.

 

[Eric W]

Sure:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	A Column	B Column			Colors	Values
2	Pink	AAA000	0		Red	AAA000
3	Blue	AAA111	1		Blue	AAA111
4	Yellow	XYZ123	0		Yellow
5	Yellow	AAA000	1
6	Red	CCC333	0
7	Red	AAA000	1
8	Red	AAA111	1
9	Orange	xyyx	0
10	Blue	AAA111	1
11	Yellow	AAA000	1		Sum of C	Single formula for C
12					6	6
Sheet11

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIF($E$2:$E$8,A2:INDEX(A:A,COUNTA(A:A)))*COUNTIF($F$2:$F$8,B2:INDEX(B:B,COUNTA(A:A)))
E12	E12	=SUM(C2#)
F12	F12	=SUM(COUNTIF($E$2:$E$8,A2:INDEX(A:A,COUNTA(A:A)))*COUNTIF($F$2:$F$8,B2:INDEX(B:B,COUNTA(A:A))))
Dynamic array formulas.

As long as there are no gaps in the A:B lists, this should work fine for you. Whenever a new row is added, the formula will include it.

 

[david_davidovich]

Okay i just realized i'm using Office Professional Plus 2016 at my workplace. So could you edit the formula for me according to this? I tried to insert it just now, but something is wrong, i guess this is the reason. I swear this is my last request. :D

Thank you very much.

 

[david_davidovich]

Okay nevermind, i managed to do it, it was only a problem of , instead of ;

Thank you very much again!!!

 

[Eric W]

Happy to help! Glad you got it sussed out.