## Im looking for some formula help please --- Again. lol

## Im looking for some formula help please --- Again. lol

(OP)

To be clear, if this can be done by making VB code that will create a function to do the job, it would be fine. My original file has a lot of formulas and no coding but I will take what I can get here.

The attached excel file is version 2007 (I think).

I need a formula that will look at a cell on one tab and count how many in a specific column in another tab there are, based on whether they have the correct criteria in another column on that tab.

I could have handled this but it gets tough here. The information on the other column can be different possibilities.

Example criteria = s01proc1

I dont know if it will always be small case or not, so this cannot be case sensitive.

It will always start with the "s" followed by either 1 or 2 numbers, followed by "proc" and ending with either 1 or 2 more numbers.

If there is any more text after this, I dont want the cells counted. If they do not fit this criterial I dont want the cells counted.

-------

This will allow us to count only cells with the correct criteria and eliminate false info.

You will notice that if you scroll down on the SIM-1 tab. Around row 450, there will be two sets of duplicates. One has two cells with false criteria that should not be counted. The other has both cells with good criteria and both will be counted.

Thanks ahead of time. I have spent the last day creating a file to compare different outputs of information for my boss but this one problem has been killing me. I could add the IF statements to a formula to check a cell if it has the correct criteria, but I cant figure out how to make it work with counting only the cells with that criteria in the adjacent cells.

The attached excel file is version 2007 (I think).

I need a formula that will look at a cell on one tab and count how many in a specific column in another tab there are, based on whether they have the correct criteria in another column on that tab.

I could have handled this but it gets tough here. The information on the other column can be different possibilities.

Example criteria = s01proc1

I dont know if it will always be small case or not, so this cannot be case sensitive.

It will always start with the "s" followed by either 1 or 2 numbers, followed by "proc" and ending with either 1 or 2 more numbers.

If there is any more text after this, I dont want the cells counted. If they do not fit this criterial I dont want the cells counted.

-------

This will allow us to count only cells with the correct criteria and eliminate false info.

You will notice that if you scroll down on the SIM-1 tab. Around row 450, there will be two sets of duplicates. One has two cells with false criteria that should not be counted. The other has both cells with good criteria and both will be counted.

Thanks ahead of time. I have spent the last day creating a file to compare different outputs of information for my boss but this one problem has been killing me. I could add the IF statements to a formula to check a cell if it has the correct criteria, but I cant figure out how to make it work with counting only the cells with that criteria in the adjacent cells.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

=COUNTIFS('SIM-1'!A:A,UPPER(E4),'SIM-1'!A:A,LOWER(E4))

I don't think this is doing all of the things you want, but you can add more items in the COUNTIFS to restrict the counting.

## RE: Im looking for some formula help please --- Again. lol

Our goal is to find any spots with that qualifying criteria are listed twice in the SIM-1 Tab. This was just counting inside the list I made.

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

for an example.... in the COMPARE sheet, scroll down to row 257. You will find spot number 39K758. The formula that would be in P257 would look for how many times that spot number is found in tab SIM-1 column A, but it would only count the instances that have the correct criteria in column I.

The problem is the correct criteria can be many different options.

Hopefully this will help. I have attached to this comment a new version of the excel file. I added a list of all the options the criteria can match in column AL of the COMPARE tab.

Maybe it will be easier to just count the instances the spot number is found in the SIM-1 tab that has criteria in column I that matches any of the options in the new list?

Thanks for looking. I appreciate it.

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Im looking for some formula help please --- Again. lol

Unfortunately I suck royally with VBA. lol

Right now, I am looking at this formula. It seems to work for its intentions. However I need to find a way to make the last part of it search the new list instead of giving it a specific text.

=SUMPRODUCT(--('SIM-1'!A2:A3000=E1477),--('SIM-1'!I2:I3000="s01proc1"))

I think I am burning out because this seems simple enough and I cant think how to do it. lol

It wouldnt work without the double dashes. What do those do?

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

They force a calculation of the expression within the parentheses, by returning 1 for TRUE or 0 for FALSE.

If you download your new workbook, with your new helper column, maybe we can come up with a solution.

It can also be written this way, which makes more sense to me...

=SUMPRODUCT(--('SIM-1'!A2:A3000=E1477)

*('SIM-1'!I2:I3000="s01proc1"))Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

Does anyone else have that problem?

Here is the URL it shows me...

Link

Thanks for pointing that out Skip. I tend to forget to double check to see if it loads like it is supposed to.

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

Ken

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

Please post for us.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

I now might have a solution, but it is using multiple helper columns. Unfortunately I am burnt out for the night and so strained I am staring at the monitor with one eye now. So I will check it again in the morning. I will try to make my trial version work with it and if it works I will post that soon.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

What I got will work to get things going, but I still need to figure out how to do this without those calumns.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

=SUMPRODUCT(--('SIM-1'!A:A=E4)*('SIM-1'!I:I=INDEX('SIM-1'!I:I,IFERROR(MATCH(E4,'SIM-1'!A:A,0),0))))

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

I am not sure if this is doing what I was looking for. I struggle with understanding SumProduct though for some reason.

When I added it, it changed the results so every spot had a 1.

Trying to figure it out, it seems to only search for the two columns, but I dont see where it calls for only those spot numbers with one of the "s01proc1 type of numbers to be counted. It allows for the spot number to be counted no matter what is in column I:I.

In column AL on the compare tab, there is a list of all the accepted proc IDs. If the cell in column I of the SIM-1 Tab is not in that list, I dont want it to be counted.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

I just realized I didnt give you a very good file to work with as it didnt have any of the spot numbers that should have failed. lol Sorry.

A good example is if at the end of the list in Comapre column A, if you will add the spot number 65K8. That number is found in the SIM-1 tab but it has bad info in column I. So the formula would need to ignore counting that one.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

"

To perform other arithmetic operationsUse SUMPRODUCT as usual, but replace the commas separating the array arguments with the arithmetic operators you want (*, /, +, -). After all the operations are performed, the results are summed as usual."

https://support.microsoft.com/en-gb/office/sumprod...

https://www.listendata.com/2012/10/why-two-minus-s...

The "*" in the following is in place of a comma that would default to multiplication.

## RE: Im looking for some formula help please --- Again. lol

I had my solution along with the column with your formula that referred to your helper columns et al. It took a long long time to calculate.

So I removed your formula and your helper columns and that helped a bit.

Then i decided to see how many Rows Excel considered to have "data" in them using a quick n dirty formula in the Immediate Window

?Activesheet.UsedRange.Rows.Count

...and I got a huge number on SIM-1

So I found the last row in Names and DELETED all rows below. Did the same on COMPARE.

Anyhow the File Size went from 5.7 MB to 90 KB.

Then I made each of your data lists, Structured Tables to limit the lookup/calculation range. So now this workbook calculates virtually immediately.

The formula in Compare:

The SUMPRODUCT basically counts each SPOT# and Operation Name combination, (including Operation Names not in the Process List) but then, included in the SUMPRODUCT is an expression that takes the Operation Name associated with the SPOT#, and does a lookup/COUNTIF in the Process List. If it's not in that list, the combination is not counted.

You can see that the INDEX() expression appears twice, associating the Operation Name with the SPOT#. You could do this on the Compare sheet if so desired.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

i had a similar situation and ended up using the index+aggregate function which met my needs.

see:

https://www.xelplus.com/return-multiple-match-valu...

and

https://www.exceldemy.com/index-aggregate-excel/

hope this helps.

-pmover

## RE: Im looking for some formula help please --- Again. lol

Thanks for that formula. It doesnt do exactly what I want, because it seems to return "1" for everything. I would only want it to count the quantity of each spot number that has info in column I on the SIM-1 list that is found in the accepted list in the compare tab on column AL. But in breaking it down and starting to understand it, I think I am beginning to see how to make it work.

Skip

I am unsure how this will work with my real file that has a lot more going on. I will email you.

pmover

I dont know if I am getting too old, or if it is just that I am juggling three other jobs at the same time right now, but those articles confused me. I will have to go back to them and understand them better when things settle down a little. Thanks.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

## RE: Im looking for some formula help please --- Again. lol

The major reason is to keep the Name and Operation Name list

referencesequivalent.The second reason is self-documentation: that is usually column headings are somewhat descriptive and can be much more helpful within a formula as opposed to A1 references.

But I do understand that there may be other more weighty reasons. I'll see what you have to say in your eMail.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

Also, I noticed that the SIM source comes with two columns with the same header name. The only difference is one is Name and the other is name. One letter us upper case. I asked about fixing this and am told they cant control it. Unfortunately both columns have info I use. lol

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

A 20 entry example is better.

The task appears to be to only count spots that have the correctly formatted OP and ignore spots that have matching spot number but not matching OP ?

A helper to identify correct op #s seems necessary.

Looks like someone unrolled a database that listed Spot#, OP number, Group and now want to undo the unroll.

Is the use to identify duplicated spot welds?

## RE: Im looking for some formula help please --- Again. lol

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

So not only do I need to find a way to count how many of each spot number there is (that has good proc info) but now I need to find a way for the other columns to look up the info only for spots that have the correct proc info as well.

In light of this, I felt it would be better to go through the file and delete any information that is not necessary to read in this comparison, and replace them with "x" in each cell and upload the real file. So you can see clearer what you are working with.

---------

The original formula I have been trying to figure is in columns "O" and "Q" on the Compare tab.

Ignore the formulas in column AK. They were just me testing things.

The new problem is in columns N and P. The formula looks for the spot number in the SIM tabs and returns the info from the ROB# column.

You will notice that spot# 33K828 in the SIM-1 tab is listed twice. In column I, one has the good proc number. The other has DELETE. Because the first one in the list has DELETE, the formula is reading that this is an error spot. This means that now I have to change these formulas to look for spot numbers with the correct proc info as well and return their info and not the info of the same spot number with bad info.

Yeesh this is turning into a nightmare. lol

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

I was using vlookup to match the spot number and return the column that had the needed info. Which grabbed the first instance. I found on line a formula that uses INDEX and SMALL to get the nth instance. Then I nested that in a few IF's.

If the first IF is blank, then go to the 2nd instance. IF that one is blank, go to the 3rd and if that is blank, just give a blank.

If we ever have a case where the same spot is in it 4 times, well we will deal with it then and I will add a 5th.

Its a long and ugly formula, but you do what works sometimes.

## CODE -->

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

This is a good test case. Tough one! I'm working on it.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

## RE: Im looking for some formula help please --- Again. lol

Since I don't understand the output requirements and the necessary transform required, I can't suggest other input transformations, but I'd also be tempted to get ALL similar input data into one table right up front.

Another thing I might do in VBA, for instance for your latest INDEX & SMALL solution, is to write

User Defined Spreadsheet Functions. They're small chunks of code that return one value given the requisite argument(s). Use them on you sheet like any other function. Much simpler to conceptualize and code than a procedure to accomplish everything from A to Z. I used to do this to get data from corporate rehosted tables, like given a Part Number, return the Nomenclature. Given a Part Number, return the Manufacturing Time etc.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

Unfortunately getting the date on one spreadsheet is not an option. We have two different groups who need to communicate any changes to where weld spots are assigned if any changes are made and such. This spreadsheet I am making is the source that will compare their outputs and see if there are any problems. To make things more difficult, they dont use the same programs, so their output spreadsheet is not in the same order nor do they have the same headers.

My spreadsheet is meant to create one list of all possible weld spots, look for those weld spots in each of their outputs, then see if they are assigned by both groups. If they are assigned by one and not the other, it needs to show that. If they are assigned in both but not to the same station, robot and gun that needs to be shown.

For some reason I just cant get the hang of VBA coding. I have basically given up trying. I just dont have enough time to focus on it consistently enough for me to retain it. My plan is to get this excel comparison working well enough for now. They have someone in their employment who can do VBA coding, but they always have him on bigger projects. I will need to press them to get him to change this over someday.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

Thanks for posting your soultion.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

i read that you have a workable solution.

Regardless, i'm looking at the "original posted" workbook matter today . . . & not the others.

sorry, but i do not understand:

"However, it will only count the cells that have the correct info in their adjacent cell in column I in the SIM-1 tab."

what is meant by "correct info"?

thanks.

## RE: Im looking for some formula help please --- Again. lol

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Im looking for some formula help please --- Again. lol

I am attaching my file that seems to work. Boss might have problems he wont tell me because I have spent so much time on it. lol This file has a lot of info replaced by X's so nothing important will be leaked.

On the Compare Tab, in column AY is a long list of possible PROC numbers. Over 10,000 it ended up being. The first two numbers in it range from 1 to 99. For each one of those, the number at the end ranges from 1 to 99. THen there are some extras added for possibilities they use 01 instead of 1, and 02 instead of 2 etc...

The only spots in the SIM tabs that we want to count are spots that has a proc number in column I that can be found in that list in the Compare tab.

My working solution was to create helper columns in the SIM tabs (which is not preferable but acceptable to make it work.

Column AD in the SIM page looks at the list on the compare tab.

Column AB will create an ID with the spot number and the Proc number together IF the cell in column AD is larger than zero.

Column AA gets the spot number back from the special ID.

Columns in the compare tab will only find info using the new spot number list. This removes spot numbers that dont have the correct proc numbers.

The COmpare tab allows us to filter to see only spots that have different assignments in different programs.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.

## RE: Im looking for some formula help please --- Again. lol

So, the initial search in COMPARE Sheet, FORMULAS column determines a number of SPOT# found in SIM-1 sheet, column A. No Problem.

Please confirm that the second part returns only those SPOT# that have the same "Process List" found in SIM-1 sheet.

For example, for the data provided, there are 3 SPOT# that match SIM-1 Sheet, Column A, i.e. Cell P254.

The next part determines if the "Process List" in row 254 is the same for the 3 SPOT# found in Cell P254.

## RE: Im looking for some formula help please --- Again. lol

If the spot number has a cell in the same row that has a proc# that matches any in the list in the COMPARE tab, it will count it as a good spot. If it doesnt, it will ignore it. If it has a matching proc# but also has other info in the same cell string as the proc# then it is still ignored. The cell with the proc# has to be an exact match of any in the list.

www.OneGodLogic.com

My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.