Does Excel have an 'IN' ?!

[DeviousDi]

Hi,

I have the following formula:

=SUMPRODUCT(--('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CU1)*('DISPUTE DETAIL'!$X$5:$X$4999<>"S01")*('DISPUTE DETAIL'!$H$5:$H$4999<>"T702"))+SUMPRODUCT(--('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CV1)*('DISPUTE DETAIL'!$X$5:$X$4999<>"S01"))+SUMPRODUCT(--('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CW1)*('DISPUTE DETAIL'!$H$5:$H$4999<>"T702")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S01"))

The problem being, is that now there isn't just 'S01', there is 'S01', 'S02', 'S03'.

In crystal I'd just use:

in['S01', 'S02', S03']

for what I need, but is there any way of doing that in excel?

Many Thanks

Di

 

[mintjulep]

For native functions there are FIND() and SEARCH().

In VBA there is InStr()

 

[SkipVought]

Hi,

Make a list on a sheet with a header value

Name this data range using the header value via Formulas > Defined Names> Create from selection > TOP row

Then use the MATCH() function to lookup a value in this list

I'll name your column X range as Val and the new lookup table as MyList...
[tt]
NOT(ISNA(MATCH(Val,MyList,0)))
[/tt]
Substitute that everywhere you have

[tt]
('DISPUTE DETAIL'!$H$5:$H$4999<>"T702")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S01")
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Oops just the (...) containing the column X range.

Sorry

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

If you only want to count rows where DISPUTE DETAIL column X is neither S01, S02 nor S03, I believe that your formula is equivalent to:
=SUMPRODUCT(('DISPUTE DETAIL'!$X$5:$X$4999<>"S01")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S02")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S03")*(
('DISPUTE DETAIL'!$H$5:$H$4999<>"T702")*(('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CU1)+('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CW1))+
('DISPUTE DETAIL'!$J$5:$J$4999=SUMMARY!$CV1)))

Brad

 

[DeviousDi]

Hi Skip,

I'm a little confused here! (Not hard, trust me!)

You say to replace:

('DISPUTE DETAIL'!$H$5:$H$4999<>"T702")*('DISPUTE DETAIL'!$X$5:$X$4999<>"S01")

with:

NOT(ISNA(MATCH(Val,MyList,0)))

After I have created my list, but how does this work when there are two different columns being used?

Thanks

Di

 

[SkipVought]

as I stated in a later post...

"Oops just the (...) containing the [highlight]column X range[/highlight]." [sub]emphasis added[/sub]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...I have lots of problems posting from my iPad, in addition to being a bit careless. Sorry !

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

okay, I'm b ack on my trusty laptop! I feel much better.

Here's my mocked up data for the DISPUTE DETAIL sheet and using Named Ranges...
[pre]
ColJ ColX ColH
SkipVought S01

SkipVought T702
SkipVought
SkipVought
SkipVought S02
[/pre]

The value in SUMMARY CU1 is SkipVought

Here's the LISTS containing values you want to EXCLUDE...
[pre]
Xvals Hvals
S01 T702
S02
S03
[/pre]

I'm only using the first SUMPRODUCT in your example
[tt]
=SUMPRODUCT(--(ColJ=SUMMARY!$CU1)*(ISNA(MATCH(ColX,Xvals,0)))*(ISNA(MATCH(ColH,Hvals,0))))
[/tt]

My result is 2

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

okay, I see that in the subsequent SUMPRODUCTS you are using values from SUMMARY in CU:CW, so ONE SUMPRODUCT mys suffice!

So the values in SUMMARY in CU:CW are SkipVought, Dan, Sid

My new test data table
[pre]
ColJ ColX ColH
SkipVought S01

SkipVought T702
SkipVought
SkipVought
SkipVought S02

Dan
Sid S03
Sid

[/pre]

My new formula
[tt]
=SUMPRODUCT(--(NOT(ISNA(MATCH(ColJ,SUMMARY!$CU1:$CW1,0))))*(ISNA(MATCH(ColX,Xvals,0)))*(ISNA(MATCH(ColH,Hvals,0))))
[/tt]

and my result is 4

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

DeviousDi,
Did you try the formula I suggested? Although shorter than your original formula, it adds the requested feature of counting only rows that contain neither S01, S02 nor S03, while still accounting for all the other criteria.

I was able to make the revised formula shorter because I replaced the three SUMPRODUCT in your original with a single one. I noticed that each of the original SUMPRODUCT was testing both S01 and T702, so that part only needed to be done once. The other criteria could then be added as required for each of the three original SUMPRODUCT.

Brad