Lazy old man needs formula to sum values by month

[MrBiggy]

Look I'm old okay? And I am one lazy old man. So hopefully you need a good deed on your list today.

Screenshot and spreadsheet are attached. Thank you for any help!

Screenshot - 10_27_2022 , 8_41_01 PM.png

[sandy666]

Hi old man
could you read big yellow banner at the top of this site?
and confirm your excel version, still 2007 ?

[MrBiggy]

Hi sandy666! Yes, still v2007. Change is hard in your old age.

Slight correction in what I wrote in the OP. I really don't need a formula for the "Month" column but would be happy to see one if you would like to provide it. It's probably a better formula than an old man can come up with.

[HansDouwe]

One way:

Please try in F3 and copy down:

Please Login or Register  to view this content.

[josephteh]

Try this: F3=SUMPRODUCT($B$3:$B$10*($A$3:$A$10>=G3)*($A$3:$A$10<=EOMONTH(G3,0))), copy down.

[MrBiggy]

Thank you HansDouwe. But the result in F3 is 186.00. It should be 685.33. It is apparently picking up the 186.00 value on 3/10/2023 of the Input table, but not the 499.33 value on 3/24/2023.

[MrBiggy]

Try this: F3=SUMPRODUCT($B$3:$B$10*($A$3:$A$10>=G3)*($A$3:$A$10<=EOMONTH(G3,0))), copy down.

Thank you josephteh! It worked! BIG thank you!

You folks are fantastic. Thank you to all!

[sandy666]

Yes, still v2007. Change is hard in your old age.

that's why I shut up good luck

[MrBiggy]

that's why I shut up good luck

Haha! Well it worked!

Okay... I got the Month column formula in the Output table to work, so I don't need that.

Everything seems to be working after I adapted the formula josephteh posted to my larger spreadsheet. GREAT JOB!

But I have one more modification I will have to make in the formula in the Total column on the output table. Tomorrow I'll modify the spreadsheet I posted here to include another column on the Input table so I can ask the question.

Thanks again for all the great help!

[bebo021999]

G3 will be 1st day of month, formatted as "mmm-yy"

Please Login or Register  to view this content.

F3:

Please Login or Register  to view this content.

IFERROR to turn 0 into blank. Use SUMIFS only if you want 0 instead of blank
Drag both down

[MrBiggy]

Thank you bebo021999!

Sorry about that. I edited my previous post after I no longer needed the formula for that column. As a matter of fact I came up with the same formula you posted and it worked! Just goes to show you that even a blind squirrel finds a nut sometimes!

But thank you and you also came up with some good ideas for me to make some improvements. Nice work!

[HansDouwe]

Please Login or Register  to view this content.

My formula also returns 685.33 in F3. See attachment

[MrBiggy]

Okay! I modified the Excel file to include another question and posted it with a screenshot below.

Can you modify the formula in Column F of the Output table so that it will only include rows that have "Joe" in the Person column of the Input table?

The new Tables...
Screenshot - 10_28_2022 , 12_08_36 AM.png

[bebo021999]

G3:

Please Login or Register  to view this content.

[MrBiggy]

First to HansDouwe ...
Thank you for following up on this. I am sure you are right. I must have made an error and offer my apologies. When I get a chance I will try your formula again to see if I can find out what I did wrong. Thanks for the great work... and you got it so fast!

bebo021999 what can I say. Another bullseye! The SUMIFS() function was one I was originally using for this spreadsheet I am working on but could not put everything together the way you did and my results were slightly off. But you NAIILED IT! I already modified the spreadsheet to adapt elements of all the formulas everyone posted here and it is now working perfectly.

So once again I want to thank all who responded.... such great answers all around! Now I go to sleep not only as an old man but as a HAPPY old man!

Thanks again all!

[MrBiggy]

HansDouwe

Ahhhh! I see what went wrong. I was in a hurry when I posted this request and needed something fast. So I didn't really look at the formulas posted or try to interpret them. I just plugged them in and tried them. Since you did not mention it when you posted the formula you gave, I did not realize it was an array formula and just entered it as a regular formula. So it did not work properly. Sorry about that. But now when I enter it as an array formula it does indeed work.

I wanted to come back here now that I had a moment to look and see what went wrong and to thank you again because you are the one who posted the first working solution and deserve the credit for that! Thank you for your excellent work! 

[josephteh]

MrBiggy, are you sure you are still using Excel 2007? I don't recall there is SUMIFS function in Excel 2007!

[MrBiggy]

Yes josephteh, still using Excel 2007. It supports SUMIFS. I have used them many times. Great function!

[josephteh]

Oh.. that's great! Thank you for your reply.