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

Occasional Contributor

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

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?

10 Replies

# Re: having problem with formula for totalling number of items received on different days in the mont

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.

# Re: having problem with formula for totalling number of items received on different days in the mont

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)

# Re: having problem with formula for totalling number of items received on different days in the mont

@Sergei Baklan I have just uploaded something.

# Re: having problem with formula for totalling number of items received on different days in the mont

@NikolinoDE I am using Microsoft Excel 365

# Re: having problem with formula for totalling number of items received on different days in the mont

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)``

# Re: having problem with formula for totalling number of items received on different days in the mont

@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

# Re: having problem with formula for totalling number of items received on different days in the mont

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.

# Re: having problem with formula for totalling number of items received on different days in the mont

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#)

# Re: having problem with formula for totalling number of items received on different days in the mont

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.

# Re: having problem with formula for totalling number of items received on different days in the mont

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