Adding AND OR into SUMPRODUCT

[Helen842000]

Hi All,

I have the following formula,

=SUMPRODUCT((ISNUMBER(SEARCH("HSBC",March!$B$4:$B$4200)))*(March!$C$4:$C$4200=C1)*(ISNUMBER(SEARCH("New",March!$D$4:$D$4200))))

This has worked perfectly for my entire spreadsheet, it counts the number of times the phrase "HSBC" is found in a report, in the same row as the employee ID located in C1 along with the term "New".

Now I have to amend this formula to include several search criteria - 10 to be exact.

This formula is an exception for this spreadsheet, it's the final thing that needs to be worked in, it ties up all the loose ends, which is why it is overly complicated. If I can add this in, it will make the spreadsheet really accurate as all the systems will be counted.

I want it to find and count every instance of several system names not just one like before. Then match it to the employee ID provided and the Action type "New". They can't be literal matches, just within the string. Phew!

The example system names are:- SIM, SAM, FTP, MRS, CBM etc.

I'm not sure whether I can just list the names, if I have to use AND/OR or a whole new formula arrangement all together.

Now, I know this would end up as a very long, messy formula. If there is a simpler way of referencing the system names required then that would be great. The system names are all listed in G1:G10.

Any pointers in the right direction would be very helpful!

Thanks!

 

[Aladin Akyurek]

Do you mean to apply those 10 criteria on March!$B$4:$B$4200?

 

[Helen842000]

Yes, the system names are listed in B4 to B4200 and contain many repetitions, so for example :-

If this is was found in the data range :-
Column B
HSBC
HSBC
HSBC
SIM
SAM

It would count all instances of the systems that also matched and in this case, return a value of 5.

 

[Aladin Akyurek]

Yes, the system names are listed in B4 to B4200 and contain many repetitions, so for example :-

If this is was found in the data range :-
Column B
HSBC
HSBC
HSBC
SIM
SAM

It would count all instances of the systems that also matched and in this case, return a value of 5.

Do the search items which G1:G10 houses occur stand-alone B-entry or as part of a B-entry in B4:B4200?

 

[Helen842000]

Thanks for your reply,

Sorry, I'm not quite sure what you mean by B-Entry. Batch entry maybe?

The search terms are just a typed list in the current worksheet and stand alone in G1:G10.

The data range is in another worksheet and the system names are in B4:B4200.

I want to find the number of times the systems described in G1:G10 are in the data range and have a final figure.

Thanks again.

 

[stefankemp]

I can't think of a shorter way than:

=SUMPRODUCT((ISNUMBER(FIND(G1,March!$B$4:$B$4200))+ISNUMBER(FIND(G2,March!$B$4:$B$4200))+ISNUMBER(FIND(G3,March!$B$4:$B$4200))+...)*(March!$C$4:$C$4200=C1)*(ISNUMBER(FIND("New",March!$D$4:$D$4200))))

 

[Aladin Akyurek]

Thanks for your reply,

Sorry, I'm not quite sure what you mean by B-Entry. Batch entry maybe?

The search terms are just a typed list in the current worksheet and stand alone in G1:G10.

The data range is in another worksheet and the system names are in B4:B4200.

I want to find the number of times the systems described in G1:G10 are in the data range and have a final figure.

Thanks again.

Does a criterion value like HSBC occur stand-alone or as part of a string in the range in column B? If this is unclear, care to post just the values from B4:B8?

 

[Expiry]

You could create a list of the names you want to include, then introduce a new column with a =COUNTIF(list,B4).

Copy that down to B100 and then do your sumproduct on that column instead of column B.

 

[Helen842000]

Hi,

The values in the data range are strings and what I am searching on is only part of that string, usually the first 3 or 4 letters.

So the values in B4:B8 could be

SIM live
HSBC test
XSAM

But I will only be searching on SIM, HSBC,SAM etc, so I can't use exact matches.

I will give both solutions mentioned a try. The new table (doing individual counts of each system and then summing them) maybe prove to be the most robust method however doing the formula FIND G1, FIND G2 would be quicker to put in and easy to replicate.

Thanks guys.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($G$1:$G$10),
          March!$B$4:$B$4200))+0,ROW($G$1:$G$10)^0)>0),
    --(March!$C$4:$C$4200=C1),
    --ISNUMBER(SEARCH("New",March!$D$4:$D$4200)))

This appears to succeed on Excel 2010.