Formula help

[Isabella]

Hi, i am after a formula that will return either OK or ERROR if the count of entries in Sheet2 and Sheet3 does not equal E6 in Sheet1

Excel Workbook
	A	E
1	Rec	Total
2	CHESS - LIFE	7
3	SMPASSET	16
4	FIXED INTEREST	2
5	CERTIFICATED	3
6	TOTAL SECURITY	28
7
8	CHECK	OK
Sheet1

Excel Workbook
	A	B
1	Rec	Age
2	CHESS - LIFE	135
3	CHESS - LIFE	138
4	CHESS - LIFE	771
5	CHESS - LIFE	714
6	CHESS - LIFE	705
7	CHESS - LIFE	384
8	CHESS - LIFE	324
9	SMPASSET	383
10	SMPASSET	134
11	SMPASSET	133
12	SMPASSET	582
13	SMPASSET	576
14	SMPASSET	569
15	SMPASSET	524
16	SMPASSET	517
17	SMPASSET	168
18	SMPASSET	715
19	SMPASSET	687
20	SMPASSET	600
21	SMPASSET	504
22	SMPASSET	495
23	SMPASSET	464
24	SMPASSET	281
25
Sheet2

Excel Workbook
	A	B
1	Rec	Age
2	FIXED INTEREST	135
3	FIXED INTEREST	138
4	CERTIFICATED	383
5	CERTIFICATED	134
6	CERTIFICATED	133
Sheet3

 

[Marcelo Branco]

Hi,

Maybe

=IF(COUNT(Sheet2!B:B)+COUNT(Sheet3!B:B)=E6,"OK","ERROR")

HTH

M.

 

[RSXchin]

=IF(OR(COUNTIF(Sheet2!A:A,A2)+COUNTIF(Sheet3!A:A,A2)<>E2,COUNTIF(Sheet2!A:A,A3)+COUNTIF(Sheet3!A:A,A3)<>E3,COUNTIF(Sheet2!A:A,A4)+COUNTIF(Sheet3!A:A,A4)<>E4,COUNTIF(Sheet2!A:A,A5)+COUNTIF(Sheet3!A:A,A5)<>E5,COUNTIF(Sheet2!A:A,A6)+COUNTIF(Sheet3!A:A,A6)<>E6),"error","ok")

 

[T. Valko]

Hi, i am after a formula that will return either OK or ERROR if the count of entries in Sheet2 and Sheet3 does not equal E6 in Sheet1

Excel Workbook
	A	E
1	Rec	Total
2	CHESS - LIFE	7
3	SMPASSET	16
4	FIXED INTEREST	2
5	CERTIFICATED	3
6	TOTAL SECURITY	28
7
8	CHECK	OK
Sheet1

Excel Workbook
	A	B
1	Rec	Age
2	CHESS - LIFE	135
3	CHESS - LIFE	138
4	CHESS - LIFE	771
5	CHESS - LIFE	714
6	CHESS - LIFE	705
7	CHESS - LIFE	384
8	CHESS - LIFE	324
9	SMPASSET	383
10	SMPASSET	134
11	SMPASSET	133
12	SMPASSET	582
13	SMPASSET	576
14	SMPASSET	569
15	SMPASSET	524
16	SMPASSET	517
17	SMPASSET	168
18	SMPASSET	715
19	SMPASSET	687
20	SMPASSET	600
21	SMPASSET	504
22	SMPASSET	495
23	SMPASSET	464
24	SMPASSET	281
25
Sheet2

Excel Workbook
	A	B
1	Rec	Age
2	FIXED INTEREST	135
3	FIXED INTEREST	138
4	CERTIFICATED	383
5	CERTIFICATED	134
6	CERTIFICATED	133
Sheet3

Try this...

=IF(SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!A2:A25,A2:A6,0))))+SUMPRODUCT(--(ISNUMBER(MATCH(Sheet3!A2:A25,A2:A6,0))))=B6,"OK","Error")

 

[RSXchin]

She's not looking at the numbers. She's counting how many of each type there are and adding the up.

 

[Marcelo Branco]

She's not looking at the numbers. She's counting how many of each type there are and adding the up.

Re-reading the OP, this is also my understanding. I'm assuming SUMIFs in E2:E5 checking the texts - CHESS-LIFE, SMPASSET...- and now she wants a double-check against the numbers entered.

M.

 

[RSXchin]

Personally, I would have had all of the column E numbers be formulas.

 

[Marcelo Branco]

Or maybe COUNTIF(s) in E2:E5...

M.

 

[T. Valko]

She's not looking at the numbers. She's counting how many of each type there are and adding the up.

Yes, I know.

That's what my suggestion does.

 

[T. Valko]

Yes, I know.

That's what my suggestion does.

OK, now I see what they're trying to do.

So, disregard my suggestion in post #4.