• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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:

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
 
Top