# IF statement help

#### Giggzz

##### Well-known Member
=IF(L5:L11="TRAINING",12,SUM(J15/L15)) is giving me #value error when the statement is true. Any idea why? Thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Zack Barresse

##### MrExcel MVP
Yes, it will fail, because you are trying to pass a linear array into a single value syntax. Why not try using something like this ..

=IF(COUNTIF(L5:L11,"Training")=7,12,J15/L15)

HTH

#### Giggzz

##### Well-known Member
Thanks that did it.... looking at the code what is the =7? always wanting to learn ....

#### babycody

##### Well-known Member
The amount of rows from L5 to L11

#### Yogi Anand

##### MrExcel MVP
So, nstead of 7, you could use ... =ROWS(L5:L11);

and if L5:L11 had been assigned a range name yRange ... you could use =ROWS(yRamge)

#### Giggzz

##### Well-known Member
How would you write it if you were to skip a cell or two in the range?

#### Scott Huish

##### MrExcel MVP
Did you want it to be true if any cell in L5:L11 is TRAINING ?

If so, perhaps:

=IF(COUNTIF(L5:L11,"Training"),12,J15/L15)

Replies
1
Views
87
Replies
0
Views
250
Replies
0
Views
248
Replies
8
Views
144
Replies
1
Views
54

1,195,920
Messages
6,012,310
Members
441,690
Latest member
CyberWrek

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back