# Sum only Currency Within Date Range Across Columns

#### marby

##### New Member
Hello all, I've attached an example file because I think what I want is complicated, though I'm hoping not.

What I want is to calculate the deposits that occurred for specific month that excludes deposits made in a different month even if the project was completed in that specific month.

So if several projects were completed in February (across 4 different weeks in February), and most were paid out in February, I want to exclude the amount paid out in March (even though the project was completed in February). And I only want to sum up the amount, not the % completed or the date. This information is in a row and it's not my spreadsheet. And the % completed, the amount and the date are required.

In the example provided, for the first ID #, 234, the total February deposit should be \$90, excluding the \$50 paid out in March for February (Week 4).

Thanks

Marby

#### Attachments

• 9.4 KB Views: 7

#### BobBridges

##### Member
If you don't want to write a function for it—I tend to write VBA at the drop of a hat, but if you don't—I guess this is how I'd do it.

#### Attachments

• 11.5 KB Views: 4

#### p45cal

##### Well-Known Member
In cell N1 put the 1st Feb 2021 (and if you want, the 1st March 2021 in cell O1) [It doesn't have to be the first of the month but the month and date need to be correct.]
In cell N4:
Code:
``=IFERROR(SUM(FILTER(\$C4:\$L4,IFERROR((MOD(COLUMN(\$C4:\$L4),3)=0)*(MONTH(\$D4:\$M4)=MONTH(N\$1))*(YEAR(\$D4:\$M4)=YEAR(N\$1))=1,FALSE),"")),0)``
which you can copy down and across.

If you have an older version of Excel in N4:
Code:
``=SUMPRODUCT(IFERROR(IF((MOD(COLUMN(\$C4:\$L4),3)=0)*(MONTH(\$D4:\$M4)=MONTH(N\$1))*(YEAR(\$D4:\$M4)=YEAR(N\$1)),\$C4:\$L4),0))``
copy down and across.

In both versions, you might get away with missing out the:
(MOD(COLUMN(\$C4:\$L4),3)=0)*

Last edited:
• herofox and marby

#### marby

##### New Member
Thank you p45cal, your formulas worked, and I'm using the first one. It's really slick, I appreciate it so much. Bob, the person actually working the spreadsheet doesn't know VBA, so that limited what I could there. But thank you both for the time you spent helping me.

M