Apr 05 2021 07:33 AM - edited Apr 05 2021 07:57 AM
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?
Apr 05 2021 07:39 AM
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.
Apr 05 2021 07:42 AM
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)
Apr 05 2021 08:07 AM
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.
Apr 05 2021 08:23 AM
Apr 05 2021 09:57 AM
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.
Apr 05 2021 10:14 AM
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#)
Apr 05 2021 10:23 AM
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.
Apr 11 2021 01:46 PM