SUMIFS

[Julier]

Hello, I am trying to use a sumifs to total up the sales between certain days a for a particular customer. my data has the day numbers acrross the top with the sales below and the customer name in column A, my tables then have the customer name and the breakdown of the days in weekly buckets, i have tried but i keep getting #Value error?

DAY NUMBERS	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
CUSTOMER A	0		5			10					10		12	5							15	16	17
CUSTOMER B	5	6							10		15	23					5			10		18
CUSTOMER C	1	5		6							8	10	23				27		29			30
CUSTOMER A
days	JANUARY
1 to 7
8 to 14
15 to 21
22 to 31
TOTAL ORDERS
CUSTOMER B
Order/delivery pattern	JANUARY
1 to 7
8 to 14
15 to 21
22 to 31

[v-sdhruv]

Hi @Julier ,
I hope you were able to resolve the issue by the solution provided.
If it has answered your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-sdhruv]

Hi @Julier ,
I hope you were able to resolve the issue by the solution provided.
If it has answered your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-sdhruv]

Hi @Julier ,
I hope you were able to resolve the issue by the solution provided.
If it has answered your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Akash_Varuna · Accepted solution]

Hi @Julier  The SUMIFS formula returns a #VALUE! error due to the table layout with day numbers in columns.Please try these

• Use Power Query to unpivot the day columns, transforming the data into a tabular format with columns for Customer, Day, and Sales.

• Apply a conditional column or filter to select the desired day range.

• Use a SUM measure in DAX:

Total Sales = CALCULATE(SUM(Sales[Value]), Sales[Day] >= 8, Sales[Day] <= 14, Sales[Customer] 

[bhanu_gautam]

@Julier Create a table that contains the day numbers. This can be done in Power BI using the "Enter Data" feature or by creating a calculated table.

DateRange = GENERATESERIES(1, 31, 1)

Create a table that contains the unique customer names. This can be done using the "Enter Data" feature or by creating a calculated table.

CustomerTable = DISTINCT('Sales'[Customer])

Add slicers to your report for selecting the customer and the date range. Use the CustomerTable for the customer slicer and the DateRange for the date range slicer.

Use DAX to create a measure that dynamically calculates the sales based on the selected customer and date range

DAX
DynamicSales =
VAR SelectedCustomer = SELECTEDVALUE('CustomerTable'[Customer])
VAR StartDay = MIN('DateRange'[Value])
VAR EndDay = MAX('DateRange'[Value])
RETURN
CALCULATE(
SUM('Sales'[Sales]),
'Sales'[Customer] = SelectedCustomer,
'Sales'[Day] >= StartDay,
'Sales'[Day] <= EndDay
)

[Julier]

Thank you for you quick response, however i need to do this in excel

[bhanu_gautam]

@Julier , Try using

excel
=SUMIFS(B2:X2, $A$2:$A$4, "CUSTOMER A", $B$1:$X$1, ">=1", $B$1:$X$1, "<=7")

=SUMIFS(B3:X3, $A$2:$A$4, "CUSTOMER B", $B$1:$X$1, ">=8", $B$1:$X$1, "<=14")

[Akash_Varuna]

Hi @Julier If this is in excel use SUMPRODUCT please 

=SUMPRODUCT(($A$2:$A$4="Customer A")*(B$1:Z$1>=8)*(B$1:Z$1<=14)*(B2:Z4))