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

Printable View

12-04-2025, 01:18 AM
k0st4din

1 Attachment(s)

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

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:

Code:

=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.

Code:

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

Thanks for the help everyone with any ideas on how to make things happen.
12-04-2025, 04:06 AM
georgiboy

Maybe you wanted:

Code:

=IF(SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTURI!$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:

Code:

=LET(t,SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTURI!$A$6:$A$65,$A9)),IF(t=0,"",t))
12-04-2025, 04:30 AM
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?

Code:

=LET(t,SUM(SUMIFS(FAKTURI!$H$6:$H$65,FAKTURI!$B$6:$B$65,FAKTURI!Q2:Q1000,FAKTURI!$A$6:$A$65,$A9)),IF(t=0,"",t))
12-04-2025, 04:35 AM
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)
12-04-2025, 06:02 AM
k0st4din

1 Attachment(s)

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"}
12-04-2025, 07:14 AM
georgiboy

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

Code:

=SUM(SUMIFS(FAKTURI!$H$6:$H$1000,FAKTURI!$B$6:$B$1000,FAKTURI!$Q$3:$Q$1000,FAKTURI!$A$6:$A$1000,$A10))
12-04-2025, 08:19 AM
k0st4din

2 Attachment(s)

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
12-04-2025, 10:43 PM
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
12-04-2025, 11:19 PM
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.
12-05-2025, 12:54 AM
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.

Code:

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.
12-05-2025, 01:30 AM
k0st4din

1 Attachment(s)

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