confused about =SUMPRODUCT(--(ISNUMBER(SEARCH

[mungojeerie]

I need two formulas to produce the number of instances a combination of data occurs. The data is a combination of numbers and text. I found a formula online which I modified to accomplish one of my needs, it works fine, although Im not sure if is exactly correct as Im not framiliar with any excel formulas other than the basics.

The data is located in up to four columns. The formula "says" show the number of instances A + B + C + D occur. The formula I have is as follows:

=SUMPRODUCT(--(ISNUMBER(SEARCH("17",$C$44:$C$542))),--($E$44:$E$542="Red"),--($D$44:$D$542="Sport"),--($F$44:$F$542="Rudder"))

Does this formula look technically correct?

Now for the variation of this formula that I still need to figure out. instead of the formula saying show the number of instances A + B + C + D occur I need it to say:

Show the number of instances A + 1 OR 2 OR 3 + x OR y OR z + etc OR ect OR ect occur.

Any help would be greatly appreciated. Thank you in advance.

Ryan

 

[barry houdini]

Your formula looks OK Ryan, welcome to MrExcel

I assume you have ISNUMBER(SEARCH because column C might contain other text (or numbers?) as well as 17?

I'm not clear on which other combinations you want to count or whether you want to count all of those together in one formula or in several formulas

Perhaps you could give some examples of what the data shows and what needs to be counted

 

[mungojeerie]

Your formula looks OK Ryan, welcome to MrExcel

I assume you have ISNUMBER(SEARCH because column C might contain other text (or numbers?) as well as 17?

I'm not clear on which other combinations you want to count or whether you want to count all of those together in one formula or in several formulas

Perhaps you could give some examples of what the data shows and what needs to be counted

Thank you for the welcome.

Below is an example of what im working with...

Column C denotes product model
Column D denotes a variation of that model
Column E denotes colour
Column F denotes another variation of that model

So my Formula is telling me how many instances of each model and variation thereof exists.

The problem is that there are also "wildcards" for example Column E denoting Color may not be as cut and dry as Red, Blue, Lime etc. There are others like "Demo" "Our Choice" "Demo-no blue" and simply cells left blank which leads to another question what do I use to say count blank cells? just simply leave the quotations empty?

I would like the new formula to either count all instances that include all possible variables or all instances that are not Red, Blue, Lime, Yellow, White.

below is a screen shot of the worksheet where the formula is drawing data from.

 

[mungojeerie]

well I fiddled around and I dont know if this is correct but it seems to work:

=SUMPRODUCT(--(ISNUMBER(SEARCH("10",$C$44:$C$542))),--($E$44:$E$542="")+--($E$44:$E$542="Demo")+--($E$44:$E$542="Our Choice"))

 

[mungojeerie]

In this formula what do the -- signifiy?

 

[mungojeerie]

Well it works to a point. It will work for Column E but If I add the variables for Column F it will count single entries as 2 entries if both columns contain a "wildcard"

It would be best if I could say count the instances of an occourance of these variables in column E or in Column F or in both Columns as one instance.

Formula Im using is as follows:

=SUMPRODUCT(--(ISNUMBER(SEARCH("10",$C$44:$C$542))),--($E$44:$E$542="")+--($E$44:$E$542="Demo")+--($E$44:$E$542="Our Choice")+--($F$44:$F$542="demo-no blue")+--($F$44:$F$542="?")+--($F$44:$F$542="*Bonus Boats*"))

so for example if an entry shows a blank in column E and in column F it says ? the formula will count that as two instances as opposed to one.

I can modify this to remove references to column F and end up with:

=SUMPRODUCT(--(ISNUMBER(SEARCH("10",$C$44:$C$542))),--($E$44:$E$542="")+--($E$44:$E$542="Demo")+--($E$44:$E$542="Our Choice"))

And this will suit 99 percent of my data.. but it would be nice to have the formula check both columns.

any thoughts?

 

[barry houdini]

Sorry, I didn't get back to you earlier,

-- converts TRUE/FALSE values returned into 1/0 which SUMPRODUCT needs to process. As you discovered you can use + effectively as an OR, when you use that you don't need -- because the + is achieving the same thing so this would suffice

=SUMPRODUCT(--ISNUMBER(SEARCH("10",$C$44:$C$542)),($E$44:$E$542="")+($E$44:$E$542="Demo")+($E$44:$E$542="Our Choice"))

 

[mungojeerie]

Sorry, I didn't get back to you earlier,

-- converts TRUE/FALSE values returned into 1/0 which SUMPRODUCT needs to process. As you discovered you can use + effectively as an OR, when you use that you don't need -- because the + is achieving the same thing so this would suffice

=SUMPRODUCT(--ISNUMBER(SEARCH("10",$C$44:$C$542)),($E$44:$E$542="")+($E$44:$E$542="Demo")+($E$44:$E$542="Our Choice"))

Ah good to know, thank you barry. Learning bit by bit