Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

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

Status
Not open for further replies.
Kenja824

Kenja824

Automotive
Nov 5, 2014
949
0
0
US
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.

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

Recommended for you

Sort by date Sort by votes
I'm not certain I'm getting all of the requirements you're asking for. I'm thinking a COUNTIFS is the function you want to use. Using the spreadsheet you attached, if in Column P of tab COMPARE you are wanting to see how many times the text in the cells in column E (Spot#) occurs in Column E of tab SIM-1, you'd use the following:
=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.
 
Upvote 0 Downvote
Thought I had it and commented as much. Realized it is doing the wrong thing.

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
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Sorry Zelgar, didnt see you had replied.


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
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Your selection criteria look rather complicated (and I have a hunch they will turn out to be even more so).[ ] If so you will probably need VBA, to make use of its LIKE operator, along with an "Option Compare Text" declaration.[ ] Warning: I have never used this in battle, having merely read about it.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]
 
Upvote 0 Downvote
I appreciate the advice Denial.

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
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
"It wouldnt work without the double dashes. What do those do?"

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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Just for the record, I think there is a glitch I am having with this site. When I start a new post and upload a file, it works. When I try to upload a file in a reply post, it acts like it is working and nothing appears.

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
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
I thought I had one earlier and realized it didnt do what I thought it did.

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.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Here is the file. I have to punch out and running late to do so, so I wont be able to post an explanation how it all works tonight. You can dissect it if you wish. The file is hopefully attached this time. lol



Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Well it is not perfect. My boss would rather they did not have helper columns on the SIM tabs because different people will be copying and pasting info to those tabs and it is too likely they will paste info over top of the helper columns.

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

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
Here's a solution without helper columns

=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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Hey Skip

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.

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


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.

Ken
My brain is like a sponge. A sopping wet sponge. When I use it, I seem to lose more than I soak in.
 
Upvote 0 Downvote
"The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible."

"To perform other arithmetic operations

Use 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."


In Excel, using two minus signs next to each other (--) converts "TRUE" to 1 and "FALSE" to 0.

Example : Let's take a simple logical condition : 3 > 2.

Apply this in Excel = 3 > 2 returns "TRUE". Using "--" (two minus signs together) with this condition returns 1 = --(3 > 2).

This method is commonly used in SUMPRODUCT formulas to convert conditional arrays that evaluate to TRUE/FALSE into 1/0. The SUMPRODUCT formula ignores non-numeric cells. Using the double minus signs technique, we can command Excel to convert TRUE/FALSE to 1/0.
Click to expand...


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

[pre]=SUMPRODUCT(
--('SIM-1'!A:A=E4)
*
('SIM-1'!I:I=INDEX(
'SIM-1'!I:I,
IFERROR(
MATCH(E4,'SIM-1'!A:A,0),
0)
)
)
)[/pre]
 
Upvote 0 Downvote
Discovered some things about your workbook that appears to be somewhat common in a workbook that has grown up like topsy.

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.
[pre]
Your List Table Name
SPOT# tSpotNo
Name tName
Operation Name tOpName
Process List tProcess List
[/pre]
The formula in Compare:[pre]
P4: =SUMPRODUCT
(
--[highlight #FCE94F](tName[Name]=tSpotNo[@[SPOT'#]])*
(tOpName[Operation Name]=
INDEX(tOpName[Operation Name],
IFERROR(MATCH(tSpotNo[@[SPOT'#]],tName[Name],0),0)))[/highlight]*
[highlight #8AE234](COUNTIF(tProcessList[Process List],
INDEX(tOpName[Operation Name],
IFERROR(MATCH(tSpotNo[@[SPOT'#]],tName[Name],0),0))))[/highlight]
)[/pre]

The SUMPRODUCT basically [highlight #FCE94F]counts each SPOT# and Operation Name combination[/highlight], (including Operation Names not in the Process List) but then, included in the SUMPRODUCT is an [highlight #8AE234]expression that takes the Operation Name associated with the SPOT#, and does a lookup/COUNTIF in the Process List[/highlight]. 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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
 https://files.engineering.com/getfile.aspx?folder=819288d5-3448-440f-8987-dee3a82ea900&file=formula_help_6.xlsx
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kenja824
  • Locked
  • Question
Formula help please
Replies
11
Views
19
SkipVought
SkipVought
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
18
BridgeSmith
BridgeSmith
Kenja824
  • Locked
  • Question
New Formula Help 1
Replies
3
Views
9
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
22
Kenja824
Kenja824
Kenja824
  • Locked
  • Question
Formula help Please
2
Replies
29
Views
30
Kenja824
Kenja824
Back
Top