Help with creating a formula

Question

Attempting to create formula that will read if the value in cell H3 is X then it will put the $ value of cell D3 into the appropriate month/year column (cells O3-Z3) based on date value in F3. Hope that makes sense.

Answer 1

Dear @Lindsay MacLeod,
Thank you for reaching out to Microsoft Q&A forum.
I understand that you're looking for a conditional calculation that dynamically maps a financial value to a specific timeframe based on both a category (cell H3) and a date (cell F3). This means you need a formula that performs an IF check followed by date-matching logic.

Assuming your column headers (O2 through Z2) contain valid Excel date values (e.g., Jan-2026, Feb-2026), you can use the following formula in cell O3 and copy it across:
=IF($H3="X",IF(AND(MONTH($F3)=MONTH(O$2), YEAR($F3)=YEAR(O$2)), $D3, 0),0)

How this logic works

• $H3="X": This acts as your primary gatekeeper; if the cell does not contain "X", the formula returns 0.
• MONTH($F3)=MONTH(O$2) and YEAR($F3)=YEAR(O$2): Ensures the transaction date matches the month and year in the column header.
• $D3: If both conditions are met (H3 is "X" AND the dates match), Excel will populate the dollar amount.

I hope this helps. If it doesn't behave as expected, feel free to share a sample structure and I’ll gladly refine the solution further. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Use an IF formula that checks H3 and the month/year of F3, and returns D3 in the matching month column, otherwise 0 (or blank).

Example for the January 2024 column in O3 (adjust the date to your needed month/year):

=IF(AND($H3="X",TEXT($F3,"mm/yyyy")="01/2024"),$D3,0)

Then in the February 2024 column (P3):

=IF(AND($H3="X",TEXT($F3,"mm/yyyy")="02/2024"),$D3,0)

Repeat across O3:Z3, changing the "mm/yyyy" part for each month/year. Copy the formulas down for other rows.

If the month/year columns are actual date headers in row 2 (e.g., O2 has 1/1/2024, P2 has 2/1/2024, etc.), a more robust pattern in O3 is:

=IF($H3="X",IF(EOMONTH($F3,0)=EOMONTH(O$2,0),$D3,0),0)

Then fill this across to Z3 and down.

---

References:

• Create conditional formulas
• Overview of formulas
• Enter a formula
• Create a formula by using a function
• Use cell references in a formula