SUMIFS but I want to know the values

[ImolAyrton]

Hello,

I used SUMIFS to get the sum of a number of cells like you can see in screenshot 1. The number 10 is circled.

This 10 is the sum of the circled numbers in screenshot 2 which are 1,1,1,1,1,1,1,1,2.

What I want to know is if there is a formula that doesnt give me the sum of 1,1,1,1,1,1,1,1,2 which is 10 but which displays the values (1,1,1,1,1,1,1,1,2) on the sheet.

stage1.png

stage2.png

[Kevin#]

How do you want them displayed?
Where do you want them displayed?
Do you want the original values in columnE (screenshot2) formatted a different colour?

thanks

[ImolAyrton]

stage7.png

I want those values (1,1,1,1,1,1,1,1,2) displayed on the same sheet as in screenshot 1. I made an example in this screenshot. I put them there manually now

[ImolAyrton]

anyone can help

[JohnTopley]

..can't view a screen shot and require data to work with so please post a small sample file.

[FDibbins]

What I want to know is if there is a formula that doesnt give me the sum of 1,1,1,1,1,1,1,1,2 which is 10 but which displays the values (1,1,1,1,1,1,1,1,2) on the sheet.

short answer - no

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[ImolAyrton]

I cant add an attachment. I uploaded the file on dropbox

https://www.dropbox.com/s/0zdhxa3xya...NENS.xlsm?dl=0

On the second sheet ''Deel 2 Intern'' I put in the numbers in
manually which I would like to get displayed there somehow through
a formula or something (1,1,1,1,2,1,1,1,1)

These numbers come from the filters I used in the first sheet called ''Registratie'' and than column F ''Inspectie_Aantal''

[FDibbins]

Not all members are able - or willing - to access file-hosting sites

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.Please upload your file to the forum.

[ImolAyrton]

I cant its too big I've tried everything from deleting data to deteling almost al sheets.

[JohnTopley]

Try ZIPping file.

[ImolAyrton]

I deleted almost al data except 2016

[ImolAyrton]

I would like the numbers in column F ''Inspectie_aantal'' on sheet ''Registration'' displayed on sheet ''Intern Deel 1'' on the matching problem where I inserted them manually

[JohnTopley]

See thr attached:

Formula in "Deel 1 Intern"

=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF(Registratie!$AF$7:$AF$2000='Deel 1 Intern'!$A$29,ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J1:J1))),"")

Enter with Ctrl+Shift+Enter

Copy across

However, to be able to do the above: we need to identify the rows of data in "Registratie" and match them against column A in"Deel1 Intern".

I did this by adding a helper column in "Registratie" which concatenated columns K, L, M to match the names in Column A i.e. Halffabricaat,Hielpand,Scheef

Obviously I could only do this because the data was filitered.

In addition, there is no test to identify the entry in "Deel 1 Intern" to be matched as the "Foutsoort" had been identified by you.

How are you going to address these issues ???

[ImolAyrton]

wow thanks. Thats really too difficult for me to understand that formula but I guess you mean that there was no column to combine halffabricaat,hielpand,scheef??

I saw you made an extra column on the end. I think you didn't see column X called ''Validatie Afwijking'' where column KLM is combined.

[JohnTopley]

Yes .. it would be possible to use that column.

The base question is: how do you filter the list - your first post implied summing data to reach 10 (or any other value)

If is simple filtered on column X (all entries for a given "Foutsoort") then we can use that column but we still need additional formulae to match X against A in "Deer 1 Intern".

[JohnTopley]

.. can I turn the query round:

Selecting a "Foutsoort" in "Deer 1 Intern" can you get all the corresponding values from column F in "Registratie" for that ""Foutsoort" ??

[ImolAyrton]

=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF(Registratie!$AF$7:$AF$2000='Deel 1 Intern'!$A$29,ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J1:J1))),"")

When I change the Registratie!$F$7:$F$2000 to Registratie!X:X I get this formula:

=ALS.FOUT(INDEX(Registratie!$F$7:$F$2000;KLEINSTE(ALS(Registratie!X:X='Deel 1 Intern'!$A$29;RIJ($A$7:$A$2000)-RIJ($A$7)+1;"");KOLOMMEN($J$1:J1)));"")

offcourse the language with my excel is in dutch. This formula only works in sheet deel 1 intern for column J. When I change Registratie!$F$7:$F$2000 into Registratie!X:X for the next column K and L it doesnt work anymore??

[JohnTopley]

=ALS.FOUT(INDEX(Registratie!$F$7:$F$2000;KLEINSTE(ALS(Registratie!$X$7:$X$2000='Deel 1 Intern'!$A$29;RIJ($A$7:$A$2000)-RIJ($A$7)+1;"");KOLOMMEN($J$1:J1)));"")


Ranges must be the same

[ImolAyrton]

doesnt work. I dont get a value

Its strange because when I select whole column X it does work for column J just not for the other ones

[JohnTopley]

Works for me:

You must enter with Ctrl+Shift+Enter

[ImolAyrton]

I dont understand where or when to use ctrl shift enter?

[JohnTopley]

Enter formula into first cell and then hold down the Ctrl and Shift keys together then hit Enter key.

If this is done correctly, you will see brackets like these { ,,,,, } appear round the formula.

You can drag the formula across the remaining cells.

[ImolAyrton]

thank you. that works now.

Problem is that since this in an example I deleted almost al data. The original file covers over 15.000 lines. Is it not possible to select whole column X using ctrl shift enter? I tried, It didnt work. Or do I just have to fill in Registratie!$X$7:$X$200000

[JohnTopley]

All the ranges need to be the same size:

=ALS.FOUT(INDEX(Registratie!$F$7:$F$20000;KLEINSTE(ALS(Registratie!$X$7:$X$20000='Deel 1 Intern'!$A29;RIJ($A$7:$A$20000)-RIJ($A$7)+1;"");KOLOMMEN($J$1:J1)));"")

Do not make ranges larger than necessary as this adversely effects performance. The above has 20K rows to allow for expansion.

If you are going to put the formula in column J for all entries then note the change in bold: I removed the $ so it will increment as you drag down. And of course change 29 to first row in "Deer 1 intern"

[ImolAyrton]

thank you. Im almost there. Now if I change the controle date on deel 1 intern to 1-2-2016 it shows me that Halffabricaat,Hielpand,Scheef only occured twice.

Now is it possible that it only shows me the values of those last 2 times. And for instance I want to check it every month and for instance in the future in the month may it occurs 20 times that when I check it than that it only shows me the values of those 20 times.

You understand what I mean? Dont know if I explained well enough in english

[JohnTopley]

If the control date is I2: I changed it and it had no impact.

There is nothing in the formula that refers to I2. The only impact (if any) would be on data in "Registratie" I.E. column F ....which would reduce the entries (from 9 to 2?)

Confused

[ImolAyrton]

When I change the date in I2 it shows me that problem Halffabricaat,Hielpand,Scheef only occured twice. You can see that in cell I29

[JohnTopley]

The fact that I29 is 2 does not change the number of entries in Column X or obviously Column F so what does change in your data that means only 2 should be selected (as I think that is what you are saying).

I think this what you want: an additional check for the date in A vs I2: which has never been mentioned so far in the thread!!!


=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF((Registratie!$X$7:$X$2000='Deel 1 Intern'!$A29)*(Registratie!$A$7:$A$2000>='Deel 1 Intern'!$I$2),ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J$1:J1))),"")

Enter with Ctrl+Shift+Enter

You will only get 2 results output.

[ImolAyrton]

Im sorry I didnt tell you in the beginning I was trying to explain what I wanted in steps because before people did not understand me

About the formula, am I supposed to put it in J29? because I get an error

=ALS.FOUT(INDEX(Registratie!$F$7:$F$2000,KLEINSTE(ALS((Registratie!$X$7:$X$2000='Deel 1 Intern'!$A29)*(Registratie!$A$7:$A$2000>='Deel 1 Intern'!$I$2),RIJ($A$7:$A$2000)-RIJ($A$7)+1,""),KOLOMMEN($J$1:J1))),"")

the highlighted gives me the error


same error when I try it with the english formula

[ImolAyrton]

already fixed it had to put ; instead of ,

[ImolAyrton]

I dragged the formula across the remaining cells. Now problem is that Halffabricaat,Hielpand,Scheef is the 29th problem. There are problems in front of it. I cant dragg a formula above for the remaining cells

[JohnTopley]

As I said earlier ...

Put this J7 (simple change A29 to A7)

=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF((Registratie!$X$7:$X$2000='Deel 1 Intern'!$A7)*(Registratie!$A$7:$A$2000>='Deel 1 Intern'!$I$2),ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J$1:j1))),"")

Enter with Ctrl+Shift+Enter

Drag across columns and down rows.

[ImolAyrton]

thank you. I forgot that on sheet registratie column AE shows RCA. I only want the values of the Internal ones in it

I also want that it filters on column C ''prd_nr'' that prd nr 1111 is not included in it

[JohnTopley]

To remove "prd nr= 1111"

=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF((Registratie!$X$7:$X$2000='Deel 1 Intern'!$A7)*(Registratie!$A$7:$A$2000>='Deel 1 Intern'!$I$2)*(Registratie!$C$7:$C$2000<>1111),ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J$1:j1))),"")

[ImolAyrton]

Thank you. It is it also possible to filter on column AE ''RCA'' only internal ones. Because its important for me I only have the problems which are caused in the factory and not problems that are caused by suppliers

[JohnTopley]

Please define precisely what you want: from the help already given you should be able to work out what needs to added (to filter on AE) to the last formula I gave you.

[ImolAyrton]

The formula is so long. I dont know where I have to add something.

On sheet 1 Registratie column AE it says ''RCA'' which has 2 choices Internal and External. I only want to show the values of the Internal ones on sheet deel 1 intern

[JohnTopley]

=IFERROR(INDEX(Registratie!$F$7:$F$2000,SMALL(IF((Registratie!$X$7:$X$2000='Deel 1 Intern'!$A7)*(Registratie!$A$7:$A$2000>='Deel 1 Intern'!$I$2)*(Registratie!$C$7:$C$2000<>1111)*(Registratie!$AE$7:$AE$2000="Internal"),,ROW($A$7:$A$2000)-ROW($A$7)+1,""),COLUMNS($J$1:j1))),"")

[ImolAyrton]

Thanks for helping it works now. Was busy last week so could not work on my excel

[JohnTopley]

If everything is now working, can you please mark thread as solved ("Thread Tools" at top of first post)

[ImolAyrton]

I have an additional question:

=ALS.FOUT(INDEX(Registratie!$AE$3:$AE$20000;KLEINSTE(ALS((Registratie!$X$3:$X$20000='Deel 1 Intern'!$A57)*(Registratie!$A$3:$A$20000>='Deel 1 Intern'!$I$2)*(Registratie!$C$3:$C$20000<>1111)*(Registratie!$AF$3:$AF$20000="Internal");RIJ($A$3:$A$20000)-RIJ($A$3)+1;"");KOLOMMEN($J$1:J51)));"")

In this formula the requirement says all data from a certain date, which is cell I2. I would like to change this to between 2 dates. cell I2 says the date from, and cell I3 says which date it ends. I highlighted this part in the formula.

[JohnTopley]

Try....

=ALS.FOUT(INDEX(Registratie!$AE$3:$AE$20000;KLEINSTE(ALS((Registratie!$X$3:$X$20000='Deel 1 Intern'!$A57)*(Registratie!$A$3:$A$20000>='Deel 1 Intern'!$I$2)*(Registratie!$A$3:$A$20000<='Deel 1 Intern'!$I$3)*(Registratie!$C$3:$C$20000<>1111)*(Registratie!$AF$3:$AF$20000="Internal");RIJ($A$3:$A$20000)-RIJ($A$3)+1;"");KOLOMMEN($J$1:J51)));"")


as I can't see the image.

[ImolAyrton]

thank you it works :D

=SOMMEN.ALS(Registratie!$F:$F;Registratie!$X:$X;'Deel 1 Intern'!A13;Registratie!$P:$P;'Deel 1 Intern'!$F$6;Registratie!$A:$A;">"&I$2)

I need the same for this it has to be between the two dates in I2 and I3

[JohnTopley]

Try

=SOMMEN.ALS(Registratie!$F:$F;Registratie!$X:$X;'Deel 1 Intern'!A13;Registratie!$P:$P;'Deel 1 Intern'!$F$6;Registratie!$A:$A;">"&I$2;'Deel 1 Intern'!$F$6;Registratie!$A:$A;"<"&I$3)

[ImolAyrton]

Yeah I tried that didnt know if it was the right way thx

[ImolAyrton]

In the attachment I put the example. I dont know if its possible but I would like a filter on the columns J to Q.

In column U, I displayed all the article numbers. I would like that If I fill in an article number in cell G2, It only displays the problems that occured with that matching article number.

[ImolAyrton]

example added

[JohnTopley]

Why not use the standard filter as there only appears to be one record per ID (in most cases as far as I can see)?

[ImolAyrton]

In the example I deleted almost al data from past years. Furthermore If I can filter on an article number it gives me information on which problems occur on which shoes (article numbers) and it gives me information about problems that occur again and again after several deliveries

[JohnTopley]

You either use the standard filter(s) or formulae on separate sheet to extract all records (rows) for a given article number where there is data in columns J to Q.

With a "helper" column in V which effectively filters on Columns J to Q

=SUM($J7:$Q7)

copy down

You can filter on this column and then on your article number or vice versa.

[ImolAyrton]

thats too fast for me. I dont understand.

This is the formula I have in cell J7:

=SOMMEN.ALS(Registratie!D:D;Registratie!$X:$X;Intern!A7;Registratie!$P:$P;'Extern '!$J$6;Registratie!$A:$A;">"&N$2;Registratie!$A:$A;"<"&N$3)

Is it possible to add something in it so I can fill in an article number in cell G2 that it shows only the problems ith the corresponding article number?

[ImolAyrton]

I tried this:

=SOMMEN.ALS(Registratie!D:D;Registratie!$X:$X;Intern!A7;Registratie!$P:$P;'Extern '!$J$6;'Extern '!U7:U326;G2;Registratie!$A:$A;">"&N$2;Registratie!$A:$A;"<"&N$3)

adding the article numbers and G2 where I put in the article number but that doesnt work

[JohnTopley]

Put this in V7 of "Extern"

=SUM($J7:$Q7)


Copy down the column

Select Columns U & V and "Filter"

Then use "Filter" non-Zero value in Column V.

Then Filter on column U for your article number.

[ImolAyrton]

Ok I've done the =SUM($J7:$Q7) and copied it down.


Select Columns U & V and "Filter"
Then use "Filter" non-Zero value in Column V.
Then Filter on column U for your article number.



can you explain that to me? I also dont know what filter non zero is

[JohnTopley]

Select columns A to V

Then "Home" ==> "Sort & Filter" ==>"Filter"

Click on "arrow" on column V and deselect 0.

Click on arrow on Column U and select required ID

[ImolAyrton]

If I select columns A to V I get a filter on all columns.. thats not what I need?

[JohnTopley]

YES ... but then

Click on "arrow" on column V and deselect 0.

Click on arrow on Column U and select required ID

[ImolAyrton]

Where does it say arrow or where do I find it

[JohnTopley]

At each filter there is square icon with arrow head on it!!!!!!

[ImolAyrton]

thank you for helping

[Kevin#]

@JohnTopley Your doggedness is to be admired. I have been watching this whole thread from the sidelines and have awarded you reps for sheer persistence

[JohnTopley]

@Kevin, Thank you very much for the feedback - really appreciate it.
,

[ImolAyrton]

excel is not easy if you dont know difficult stuff and im not english!!

[Kevin#]

@ImolAyrton totally agree! Most English speakers do not appreciate how lucky they are.

[ImolAyrton]

well thannks for helping, Im marking it as solved