Adding a new product to a product database and exporting the amount

[k0st4din]

Hello everyone,
I have been using this table for many years, in which I have 2 worksheets and in one all expenses (invoices) are recorded.
In the worksheet Fakturi , I have a column Q , in which I add if there are new things, products or whatever.
Then, through a drop-down menu in column B6:B65, these products come out to be selected.
So far everything is OK.
Now, however, in the worksheet SLUJITELI , in column G8:G38, the expense (amount) is displayed according to the corresponding date.
To make things happen, use this formula in the cells:



=IF(SUM(SUMIFS(FAKTURI!$H$6:$H$65;FAKTURI!$B$6:$B$65;{"PRODUCT 1";"PRODUCT 2";"PRODUCT 3";"PRODUCT 4";"PRODUCT 5"};FAKTURI!$A$6:$A$65;SLUJITELI!$A8)))=0;"";SUM(SUMIFS(FAKTURI!$H$6:$H$65;FAKTURI!$B$6:$B$65;{"PROUKT 1";"PRODUCT 2";"PRODUCT 3";"PRODUCT 4";"PRODUCT 5"};FAKTURI!$A$6:$A$65;SLUJITELI!$A8)))


My problem here is that if an employee in the file needs to write a new product in sheet FAKTURI, column Q, if I don't enter it (add it) in the formula, it doesn't show up and doesn't calculates. This is because the file is locked so that no one can delete anything (completely normal).


Here is the problem: how can any employee add a new product to column Q, somehow be able to add it to the formula and calculate it, without the employees unlocking the file, somehow things happen automatically?

I tried to somehow change the formula, but things aren't working out for me and I'm definitely messing something up somewhere.


=IF(SUM(SUMIFS(FAKTURI!$H$6:$H$65;FAKTURI!$B$6:$B$65;{FAKTURI!Q2:Q1000};FAK TURI!$A$6:$A$65;SLUJITELI!$A8))=0;"";SUM(SUMIFS(FAKTURI!$H$6:$H$65;FAKTURI!$B$6:$B$65;{FAKTURI!Q2:Q1000};FAKTUR I!$A$6:$A$65;SLUJITELI!$A8)))

Thanks for the help everyone with any ideas on how to make things happen.

[georgiboy]

Maybe you wanted:

=IF(SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTU RI!$A$6:$A$65,$A10))=0,"",SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTURI! $A$6:$A$65,$A10)))

Or:

=LET(t,SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FA KTURI!$A$6:$A$65,$A9)),IF(t=0,"",t))

[k0st4din]

Hello, now I'm going to try to see if things will work out.
I was wondering in the second formula, where does this "t" come from and what does it mean?

=LET(t,SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FA KTURI!$A$6:$A$65,$A9)),IF(t=0,"",t))

[georgiboy]

It will only work in the newer versions of Excel, Excel 365.

Within the LET function, you can set variables, for example:
t = SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTURI!$ A$6:$A$65,$A9))
Now that t holds the result of that formula, you can use: IF(t=0,"",t)

[k0st4din]

Hello, something is not working for me.
I'm uploading the table again with your new formula.
I have values ​​for dates 4th and 3rd, but it doesn't show me the values.
Can you take a look to see if I've made a mistake somewhere?
The test is with the first formula.


Thanks in advance.
P.S. Another thing, in your formula I somehow don't see the column &Q$3:$Q$1000, from where the comparison and, accordingly, the addition of other products will be made.
To avoid this additional post
{"PROUKT 1";"PRODUCT 2";"PRODUCT 3";"PRODUCT 4";"PRODUCT 5"}

[georgiboy]

The below, gave me a result. I have made all of the ranges being checked the same size:

=SUM(SUMIFS(FAKTURI!$H$6:$H$1000,FAKTURI!$B$6:$B$1000,FAKTURI!$Q$3:$Q$1000, FAKTURI!$A$6:$A$1000,$A10))

[k0st4din]

Hello again,
I think you don't see my formula well.
It takes and searches in column B in the Fakturi worksheet the words that until now, if there is a new product, I added and wrote by hand in the SLUJITELI worksheet in column G range.
I try not to do this. And even if a macro could add new products when they are added to the Fakturi worksheet and return the sum, because - Yes, it gives a result but it is equal to zero, because it doesn't know what to find.
I will attach pictures.
Maybe something is confusing you, because the result cannot be given to me as a sum of 0.
On 3.11 and 4.11 in the example I have Product 4 and Product 3 in the Fakturi sheet, respectively in the SLUJITELI sheet, I should have the sum of 35(G10) and 150(G11), and it gives me a result of 0 (because it doesn't know what to find and what to search for in the database from column Q

[Aussiebear]

Instead of using defined ranges like Q2:Q1000, try naming the range as a table. Simply add your new product to the table and the formulas should all adjust automatically

[k0st4din]

Hello, you want to tell me to use my formula again, only to put the given name in the special quotes. Is that how I understand it, i.e. instead of {Product 1, Product 2}, it should have some name, for example {Goods}. In the other worksheet, I only look for the money, but as I wrote at the beginning, if a new product is inserted, I always have to go to the Slujiteli sheet and add the product by hand in the formula. I am looking for an alternative to make things happen automatically. Please look at my formula in the first question. Thank you very much.

[k0st4din]

Hello,
I'm trying to do this with a macro for example.
The idea is that I add a new product in a column from Q3 down and it automatically "enters" the drop-down menu to be selected in a cell in column B6:B65 in the "Fakturi" sheet, then in the other sheet Slujiteli, in column G8:G38 according to the date, only the amount can be calculated and I can only see it (regardless of how many products there are for a given date).
I say again, now I do it with the formula shown in my first post, but every time someone adds a new product in column Q, the product is not visible and I have to add it manually to start calculating it in the Slujiteli sheet in column G8:G38.

This is a macro made by hand because I can't make it 100% correct, but you can at least see what my idea is.

Sub addnewproduct()


Sheets("FAKTURI").Select
ActiveCell.FormulaR1C1 = "next product add 1"
Range("Q11").Select
ActiveCell.FormulaR1C1 = "next product add 1"
Range("Q12").Select
Sheets("SLUJITELI").Select
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUM(SUMIFS(FAKTURI!R6C8:R65C8,FAKTURI!R6C2:R65C2,{""next product add 1"";""next product add"";""PRODUCT 1"";""PRODUCT 2"";""PRODUCT 3"";""PRODUCT 4"";""PRODUCT 5""},FAKTURI!R6C1:R65C1,SLUJITELI!RC1))=0,"""",SUM(SUMIFS(FAKTURI!R6C8:R65C8,FAKTURI!R6C2:R65C2,{""next product add 1"";""next product add"";""PROUKT 1"";""PRODUCT 2"";""PRODUCT 3"";""PRODUCT 4"";""PRODUCT 5""},FAKTURI!R6C1:R65C1,SLUJITELI!RC1)))"
Range("G8").Select
Selection.AutoFill Destination:=Range("G8:G38"), Type:=xlFillDefault
Range("G8:G38").Select

Range("G8").Select
Selection.Copy
Range("G9:G38").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("B8").Select
End Sub


I don't know if I'm explaining it correctly.
Thanks in advance for any help from you.

[k0st4din]

Aussiebear,
I tried your suggestion, but again the amount doesn't work for me.
32193

[georgiboy]

Out of interest, what version of excel do you run this spreadsheet on?

[k0st4din]

Personally, I use Excel 2007, but this formula has never given any computer a problem, regardless of what Excel was used.
I'm curious how these brackets { } do this "focus"

[georgiboy]

Anything between the {} is an array, Excel only allows static values in a formula array. I am not saying you will get a problem with the formula, there are just much easier ways of doing things in the latest versions of Excel. Functions like UNIQUE and other array formula that can be referenced in data validation etc.

[k0st4din]

Okay, can we find a solution or will I have to continue the old way?

[georgiboy]

So is the formula in the attached not giving you a result?

[georgiboy]

I think it should be FAKTURI!$Q$2:$Q$1000 instead of FAKTURI!$Q$3:$Q$1000 also

[k0st4din]

Oh, oh, oh, it will work of course, because I was an oligophrenic with this small detail of these { } and to press ctrl + shift +enter


Thank you very much and I apologize for what I did
You have no idea how grateful I am.
Be alive and well and still so dedicated.
A bow to you.
Greetings