formaula calculate 2 cells

[junkforhr]

I have conditional statement {=SUM(IF('name of sheet'!$H$2:$H$226=H8,1,0))}

When ever I try to add a 2nd condition in using the wizard, I get incorrect results.

H8 is the cell that I want the calculation based on. How do I put on there that I want the calculation based on H8 or H9. If the answer is an easy one I apologise in advance. I'm a newbie to excel.

 

[the_original_invisible]

Something like this?

=SUM(IF(OR('name of sheet'!$H$2:$H$226=H8,'name of sheet'!$H$2:$H$226=H9),1,0))

Lee

 

[Richard Schollar]

Hi

You could use:

=SUM(COUNTIF('name of sheet'!$H$2:$H$226,H8:H9))

which requires array-entering with CSE.

 

[Richard Schollar]

Something like this?

=SUM(IF(OR('name of sheet'!$H$2:$H$226=H8,'name of sheet'!$H$2:$H$226=H9),1,0))

Lee

Lee, I'm afraid you can't use an OR construct in this instance as OR doesn't return an array (it just returns either TRUE or FALSE) which is very annoying. AND works in a similar manner too.

 

[junkforhr]

Thanks Richard, that works great, but I just realised, what if I want to add cells that are not adjacent , so instead of H8 and H9, I need to add H8 and Z30. I have no control over the data that is presented to me.

Cheers for your help so.

 

[LearnMeExcel]

Hi Richard
i see this formula using Or in array formula

Excel Workbook
	A
1	5
2	3
3	6
4	5.5
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
and u said

Lee, I'm afraid you can't use an OR construct in this instance as OR doesn't return an array (it just returns either TRUE or FALSE) which is very annoying. AND works in a similar manner too.

how does it work ?

 

[Richard Schollar]

Thanks Richard, that works great, but I just realised, what if I want to add cells that are not adjacent , so instead of H8 and H9, I need to add H8 and Z30. I have no control over the data that is presented to me.

Cheers for your help so.

You can do it like this, but using this formaul you need to know if your values in eg H8 and Z30 contain either Text or Numbers.

Excel Workbook
	A	B	C	D	E	F
1	1			0
2	2
3	3					3
4
5	1			4
6	2
7	3
8	0
9	1
10	2
11	3
12
13
14	Richard			Sally
15	Ben					John
16	Terry
17	Sally
18	Richard			3
19	Ben
20	Terry
21	John
22	Richard
23	John
24	Terry
Sheet1

Excel 2002

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Richard Schollar]

Hi Richard
i see this formula using Or in array formula

Excel Workbook
	A
1	5
2	3
3	6
4	5.5
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
and u said

how does it work ?

I'm sorry, but I don't see what relevance the formula you posted has to the statement I made?

 

[LearnMeExcel]

u said we can't use or in arrary Formula

 

[junkforhr]

Thanks Richard, working perfectly!