If statement with selected range

honeyherrada

Board Regular
Joined
Apr 18, 2007
Messages
56
Hello,

I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.

Please help!

Thanks!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hello,

I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.

Please help!

Thanks!

=SUMPRODUCT(--(F2:F4<>'2007Open'!H2:H4),--('2007Open'!B2:B4=8))
 
Upvote 0
Thanks...but it's giving me the #N/A error: here's what my code looks like:

=SUMPRODUCT(--(department_code<>'2007Open'!H5330:H5333),--('2007Open'!B5330:B5333=8))

the "department_code" is the name of the range cells that I need to compare to the other cells in 2007Open sheet.

Thanks!!
 
Upvote 0
Can I not not use the SUMPRODUCT function as the "department_code" range cells is not parallell to the cells i'm comparing it to? the the department_code ranges from A2:A50
 
Upvote 0
Can I not not use the SUMPRODUCT function as the "department_code" range cells is not parallell to the cells i'm comparing it to? the the department_code ranges from A2:A50

The relevant ranges must be equally sized...

Perhaps, you are looking for:

=SUMPRODUCT(1-ISNUMBER(MATCH('2007Open'!H5330:H5333,department_code,0)),--('2007Open'!B5330:B5333=8))
 
Upvote 0
Hi Aladin,

I tried the formula you had given me but i believe this formula matches and sums up all the codes that are on another worksheet. What i want it to do is to find all the rows that are not a match based on branch number, in this case 8, and that falls between the date of "200701??"
 
Upvote 0
Hi Aladin,

I tried the formula you had given me but i believe this formula matches and sums up all the codes that are on another worksheet. What i want it to do is to find all the rows that are not a match based on branch number, in this case 8, and that falls between the date of "200701??"

What the formula does is: Count all records where a H-value is not in department_code and a B-value is an 8. If this is not what you want, try to elaborate.
 
Upvote 0
Hi Aladin,

Nevermind my previous comment...this works! I had to make the cells absolute.

Thanks for you help! You're awesome!!
 
Upvote 0
Hi Aladin,

One more question...how do I include the specific dates I'm looking for in this formula? I only want to look at the non-matching items with the dates between "200701??"

=SUMPRODUCT(1-ISNUMBER(MATCH('2007Open'!$H$2:$H$6917,department_code,0)),--('2007Open'!$B$2:$B$6917=8))
 
Upvote 0
Hi Aladin,

One more question...how do I include the specific dates I'm looking for in this formula? I only want to look at the non-matching items with the dates between "200701??"

=SUMPRODUCT(1-ISNUMBER(MATCH('2007Open'!$H$2:$H$6917,department_code,0)),--('2007Open'!$B$2:$B$6917=8))

=SUMPRODUCT(1-ISNUMBER(MATCH('2007Open'!$H$2:$H$6917,department_code,0)),--('2007Open'!$B$2:$B$6917=8),--(DateRange=Date))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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