if cell has a number calculate formula

Thomas Martin

New Member
Joined
Mar 2, 2018
Messages
14
can anyone help me with formula?

if cell range I6:I1000 has a 2 in it the calculate =AGGREGATE(16,7,$AL$6:$AL$1001,0.9)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
calculate the formula if the cell had a particular number such as 2 in cell range I6:I1000, 2 being the trigger to start calculating...
 
Upvote 0
Thomas Martin, Good afternoon.

Try to use:

=IF(COUNTIF(I6:I1000,2)>0, YOUR ANOTHER FORMULA, "")

Is that what you want?

I hope it helps.
 
Upvote 0
ok so maybe it didn't work... I used =IF(COUNTIF(I7:I1000,2)>0,AGGREGATE(16,7,$AL$7:$AL$1000,0.9))
and it calculates if there is any number in column I7:I1000


I only want it to calculate if there is a number 2 in the column
 
Upvote 0
it calculates if there is any number in column I7:I1000
It shouldn't.

If you look at this part of the equation:
Code:
[COLOR=#333333]COUNTIF(I7:I1000,2)[/COLOR]
It is specifically counting the number 2's in that range
and the IF part is checking to see if that count if greater than zero.

If you run that formula I posted above by itself, what does it return?
This will tell you how many 2s it is finding in your range.
If it is greater than zero, it will do your formula.
If it is greater than zero, but you think it should be zero, check again. You may be missing/overlooking the two.
You can find where it is by selecting your range, pressing CTRL-F to bring up find, enter 2 for the value you are looking for, then press the Options button and make sure the "Match entire cell contents" check box is checked, and press "Find Next". This will bring you to the first 2 it is finding in your range.
 
Upvote 0
You left off the false argument so if 2 is not found your formula will return FALSE, If that is what is happening then put in "" to return blank.

Code:
=IF(COUNTIF(I7:I1000,2)>0,AGGREGATE(16,7,$AL$7:$AL$1000,0.9),"")
 
Upvote 0
it returns 4 as a count

But I want the formula to only calculate the cells that are found with a 2 in I7:I1000
That is telling you that there are 4 cells in your range that are equal to 2.
Since that is greater than 0, it is doing the calculation you wanted.

Perhaps you weren't too clear on what it was you are looking for.
Rather than doing that exact expression if ANY cells in your range are equal to 2, do you actually mean to only include the rows that are equal to 2 in your calculation?

Perhaps you can clarify it by posting a small, simple example along with the expected results.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top