# I have part of a formula already written..need to add another part..

#### Marq

##### Well-known Member
How would I complete this formula...I had help from the baord with the part I have alreay but I want to add another equation into it:

I would like to put in cell M39 to say the following:

If the value in M38 is greater than ZERO then leave M39 blank....BUT if the value of cell M38 has no value then COUNTIF(\$Z\$1:\$AC\$6,M38-1)+COUNTIF(\$Z\$1:\$AC\$6,M38+1),0)

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### T. Valko

##### Well-known Member
How would I complete this formula...I had help from the baord with the part I have alreay but I want to add another equation into it:

I would like to put in cell M39 to say the following:

If the value in M38 is greater than ZERO then leave M39 blank....BUT if the value of cell M38 has no value then COUNTIF(\$Z\$1:\$AC\$6,M38-1)+COUNTIF(\$Z\$1:\$AC\$6,M38+1),0)
Try this...

=IF(M38>0,"",SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1})))

#### Marq

##### Well-known Member
Try this...

=IF(M38>0,"",SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1})))

I do not understand the role of the "SUM".....I am not summing up anything..I am "counting"

#### Marq

##### Well-known Member
Try this...

=IF(M38>0,"",SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1})))

It does not get me the correct answer...i think the SUM may be the issue maybe??

#### T. Valko

##### Well-known Member
It does not get me the correct answer...i think the SUM may be the issue maybe??
SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1}))

Does exactly the same thing as

COUNTIF(\$Z\$1:\$AC\$6,M38-1)+COUNTIF(\$Z\$1:\$AC\$6,M38+1)

It would also be the same as:

SUM(COUNTIF(\$Z\$1:\$AC\$6,M38-1),COUNTIF(\$Z\$1:\$AC\$6,M38+1))

If you're getting an incorrect result then we need a more detailed explanation of what you're wanting to do.

#### Marq

##### Well-known Member
SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1}))

Does exactly the same thing as

COUNTIF(\$Z\$1:\$AC\$6,M38-1)+COUNTIF(\$Z\$1:\$AC\$6,M38+1)

It would also be the same as:

SUM(COUNTIF(\$Z\$1:\$AC\$6,M38-1),COUNTIF(\$Z\$1:\$AC\$6,M38+1))

If you're getting an incorrect result then we need a more detailed explanation of what you're wanting to do.

that part is working fine....its the reading of cell M38 if its greater than zero part that isnt working it seems.

What I'm trying to say in the formula in cell M39 is "If cell M38 is greater than ZERO then dont do anything..leave cell M39 blank...BUT if M38 has no value THEN SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1}))

#### T. Valko

##### Well-known Member
that part is working fine....its the reading of cell M38 if its greater than zero part that isnt working it seems.

What I'm trying to say in the formula in cell M39 is "If cell M38 is greater than ZERO then dont do anything..leave cell M39 blank...BUT if M38 has no value THEN SUM(COUNTIF(\$Z\$1:\$AC\$6,M38+{1,-1}))
If M38 has no value then M38 evaluates to 0 and 0+{1,-1} = {1,-1}.

So, if M38 has no value then the formula will COUNT cells in the range that contain 1 and -1.

Is that what you want?

#### Marq

##### Well-known Member
If M38 has no value then M38 evaluates to 0 and 0+{1,-1} = {1,-1}.

So, if M38 has no value then the formula will COUNT cells in the range that contain 1 and -1.

Is that what you want?

yes..if M38 has no value the count the number of cells in the range Z1:AC6 that are within 1 (plus 1 or minus 1) of cell M15

#### T. Valko

##### Well-known Member
yes..if M38 has no value the count the number of cells in the range Z1:AC6 that are within 1 (plus 1 or minus 1) of cell M15
OK, now you just changed things based on the condition of cell M15!

Try this...

=IF(M38>0,"",IF(M38="",SUM(COUNTIF(\$Z\$1:\$AC\$6,M15+{1,-1})),""))

I'm assuming that M38 will only contain a number or be an empty/blank cell.

#### Marq

##### Well-known Member
OK, now you just changed things based on the condition of cell M15!

Try this...

=IF(M38>0,"",IF(M38="",SUM(COUNTIF(\$Z\$1:\$AC\$6,M15+{1,-1})),""))

I'm assuming that M38 will only contain a number or be an empty/blank cell.

still not working...could I please send you my example sheet in email?

Replies
5
Views
212
Replies
12
Views
292
Replies
3
Views
647
Replies
5
Views
65
Replies
2
Views
413

### Forum statistics

1,191,669
Messages
5,987,949
Members
440,121
Latest member
eravella ### 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