Forum Discussion
SUMIFS
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 |
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]
8 Replies
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
)Thank you for you quick response, however i need to do this in excel
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))
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]
