Formulars Issues

BAC

Dear Community,

I am working on our sales dashboard, and I would like to illustrate a graph showing our contract signing date (years), with a contract value (see picture below).

I have the following two tables in my master sheet.

The following is created in my intermediate sheet.

I have tried several formulas, but I still can't figure out, why it's not working. Could it be that the way my dates are entered?

Is there anyone who can help me with the formula?

Paul Newcome

What is the formula you currently have?

BAC

Latest Formula, but I also know this might be completely wrong;

=COUNTIFS({Master Sheet - value 2}:{Master Sheet - value 2}, AND{Master Sheet - Decision Matrix Range 1}, >=DATE(2024, 1,1))

=COUNTIFS({Master Sheet - value 2}, {Master Sheet - Decision Matrix Range 1}, >DATE(1, 1, 2024))

BAC

=SUMIFS([Master Sheet - Value 2]:[Master Sheet - Value 2],           [Master Sheet - Decision Matrix Range 1]:[Master Sheet - Decision Matrix Range 1],           ">=" & DATE(YEAR(Labels@row), MONTH(Labels@row), DAY(Labels@row)),           [Master Sheet - Decision Matrix Range 1]:[Master Sheet - Decision Matrix Range 1],           "<=" & DATE(YEAR(Labels@row) + 1, MONTH(Labels@row), DAY(Labels@row)))

Paul Newcome

You are closest with your COUNTIFS. What are each of the {Cross Sheet References} covering?

BAC

Hi Poul,

It covers two important aspects: First, it indicates the value of the contract associated with the sales opportunity/customer. Second, it outlines when we expect to finalize an agreement (contract) with the sales opportunity/customer.

Paul Newcome

I mean exactly what data points are the two cross sheet references in your COUNTIFS covering? When you created them, what did you select exactly?