IF FUNCTION

JHELP

New Member
Joined
Sep 24, 2006
Messages
4
IM AM TRYING TO RUN AN IF FORMULA ON MORE THAN 1 CELL

EXAMPLE MY CURRENT FORMUL WORKS ON 1 CELL
=IF((AND(D7=D10,E7=E10,F7=F10,G7=G10,H7=H10)),"YES","NO")

I NEED TO RUN IT ON ROWS VERSUS CELL

EX

=IF((AND(D7=D10:D22,E7=E10:E22,F7=F10:E22,G7=G10:G22,H7=H10"H22)),"YES","NO")

DOESNT WORK PLEASE HELP I HAVE 24 HRS BEFORE THIS IS DUE I WILL EAMIL THE SPREADSHEET IF THAT WILL HELP!!!!

AM I USING THE WRONG FUNCTION
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
JHELP,

It's hard to tell what "=" means. If D7 is blank, do cells that evaluate to zero "=" D7? Does the sum of all values in D10:D22 need to equal the value of D7? Does each cell in the range D10:D22 need to individually equal D7? Will D7 and D10:D22 hold numbers or text?

Dufus
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
A couple of things:

You have quotation marks instead of the colon for H10:h22. If you copied it from your formula that will be an error

Try Ctrl+shift+enter to enter it as an array formula. I entered your formula into a blank worksheet with CSE and it showed up as yes, which is right as blank = 0

Finally, Turn off Caps lock as it gives the impression you are shouting at people

Good luck
 

JHELP

New Member
Joined
Sep 24, 2006
Messages
4
Thank you to all. I think the problem is :) I am trying to use an if function to determine if any cell the row ='s a single cell. I get "yes" as long as all the cells in the row match the "=" cell. If they do not I get no. I want to get a "yes" if any cell in the row matchs the "="cell.

example = if c7 ='s any cell in row c8:c25 i get a yes versus a no



am i making any sense??? im having a hard time understanding myself at this point
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94

ADVERTISEMENT

One way would be with COUNTIF

e.g.

=IF((AND(COUNTIF(D10:D22,D7)>0,COUNTIF(E10:E22,E7)>0,COUNTIF(F10:F22,F7)>0,COUNTIF(G10:G22,G7),COUNTIF(H10:H22,H7)>0)),"YES","NO")
 

JHELP

New Member
Joined
Sep 24, 2006
Messages
4
brother thank you sooo much you are a god sent and i owe you man you have wonderful day and thank you soooooooo much for taking the time out to help me thank you thank you
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Thank you to all. I think the problem is :) I am trying to use an if function to determine if any cell the row ='s a single cell. I get "yes" as long as all the cells in the row match the "=" cell. If they do not I get no. I want to get a "yes" if any cell in the row matchs the "="cell.

example = if c7 ='s any cell in row c8:c25 i get a yes versus a no



am i making any sense??? im having a hard time understanding myself at this point

=IF(ISNUMBER(MATCH(C7,C8:C25,0)),"Yes","No")
 

Forum statistics

Threads
1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

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
Top