Forgot to mention I cannot use pivot tables or vba for this. 

Hi Payal,

The WEEKDAY function requires a date (serial number), you're providing it with a day of the month number. It cannot distinguish which day of the week the number 1 falls on without the context of month and year i.e. the full date.

The modern form of SUMPRODUCT is SUMIFS.

Mynda

Thanks I know. I tried using sumproduct function with indirect and weekday but I cannot get it right. 

Somehow I need to link the month and year - Jan-20 in the workings tab with the householdusage data day tab to give me a range which gives me dates as 1/1/2020 to 31/1/2020 and converts it into a weekday.

this is the formula I have which is incorrect. 

=-(SUMPRODUCT(--(WEEKDAY('HouseholdUsageData (kWh)'!$A$2:$A$1489,2)>Assumptions!$G$34),--('HouseholdUsageData (kWh)'!$AB$2:$AB$1489>Assumptions!$G$39),--('HouseholdUsageData (kWh)'!$AB$2:$AB$1489<=Assumptions!$H$39)*INDIRECT("'"&$D$76&"'!"&L$83)))

Can you assist please?

You can try using the DATE function to build the date inside the WEEKDAY function e.g.

--(WEEKDAY(DATE(YEAR(L7),MONTH(L7),'HouseholdUsageData (kWh)'!$A$2:$A$1489),2)<=Assumptions!$G$34)

Thank you so so much. Worked like a charm! I complicated it in my head. 

I need to build scenarios for different plans and consumption scenarios. Was using data tables for that in the assumptions tab but I am getting the same values. Basically I need to get a snapshot of all the combinations so that I can build an interactive dashboard for the costs.

Is it because the householdconsumption data (input) and the workings (output) are in a separate tab? It is just not working!

Appreciate all your help on this. 

regards

Hi Payal,

I doubt it has anything to do with the data being on separate tabs. You haven't been specific about what isn't working.

Mynda

My apologies. I should have been more specific. I have built 2 single variable data table - for a selected scenario, what is the average monthly cost for each of the plans.(2) For a selected plan, what is the average monthly cost for each of the scenarios. I am getting stuck in the combination matrix - 2 variable data table.

The 2 single variable data table are working fine. 

I have built the data tables in the assumptions tab. Please find attached the assumptions page with the data table structure (row 98). I have scenarios in the column and the plans in the row. At the intersection i have =Workings'!F73 (linking to average monthly cost). What i am looking for is for various plans, I can find the monthly average cost for each of the scenarios. Where am I making the mistake? Please let me know if you need any more information. Unfortunately, I am not able to upload the full file. 

Cheers

Payal

Hi Payal,

The 2 variable tables require two inputs that are numbers, as explained here. The variable row 98 contains text. This needs to contain numbers. Likewise column D. See example attached.

Mynda

P.S. If you have further question unrelated to these ones, please start a new thread so that others can also find the answers easily.

Thanks Mynda. I know it has to be a number. But if we put a reference number which links to the names of the plans and consumption pattern it works. I got it finally. I was linking the wrong cells for column and row input.

Thank you heaps for all your help on this. You guys are brilliant. 

Cheers