having problem with formula for totalling number of items received on different days in the month

[pachesta]

Hi, I am trying input a formula that works for totalling number of items received on different days in a month.  I have set up a worksheet that counts items raised on each day in the main spreadsheet.  However, I want to work out the total value of items raised for each day.  I've tried sum product and various others, but nothing works, instead it just returns a "0" which isn't right.  Can you tell me where I am going wrong?

[#2]

@pachesta 

That's bit abstract, perhaps you may illustrate on the sample. It you'd like to aggregate for each date most probably it's better to use PivotTable.

[#3]

@pachesta 

With your permission, it can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

Knowing the Excel version and operating system would also be an advantage...for you.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#4]

@Sergei Baklan I have just uploaded something.

[#5]

@NikolinoDE I am using Microsoft Excel 365

[#6]

@pachesta 

Thank you. I'm not sure that exactly do you mean under "total value of requistion raised", perhaps formula could be

=SUMIF(Sheet2!A:A,A2,Sheet2!G:G)

Please check attached.

[#7]

@Sergei Baklan. Thanks. It works but I wanted it to calculate automatically anything that I add to the main spreadsheet for each day. Eventually, I wanted to do another workbook that shows the data for each month. So for all transactions for April, I wanted to show the number of transactions and the total amount of those transactions for each day. Sorry if I'm not being very clear

[#8]

@pachesta 

To show result for the date it's better to use Excel Tables, it automatically expand calculations with expanding the table

To show totals for the month it depends on how you'd like to select the month (in general an year as well), will it be separated table with totals for each yearmonth, or you select them from drop-down list(s) or what.

[#9]

@pachesta 

If you use Excel Tables for manual data input, the data ranges grow automatically as data is added.

By using a dynamic array for the dates in the daily summary, that too will grow as required.

The formulas I used were

EndDate: =MAX(PO[Date])

Date: =SEQUENCE(1+endDate-startDate, 1, startDate)

RequisitionCount: = COUNTIFS(PO[Date], Date#)

RequisitionValue: =SUMIFS(PO[Amount (ex.VAT)], PO[Date], Date#)

[#10]

@pachesta 

Hi there. Seems Pivot table would be the fastest solution here. Is there a particular reason why would you like to have monthly data in a separate Workbook? Please see file attached. There is an extra sheet added where monthly data is aggregated and also a Pivot Table which shows daily and monthly data.

[#11]

thank you. I think I've sorted it now.