Sumifs with different column

[teuteux13]

Dear all,

I need to found the formula to obtain the sum of previous prices month in the column total.

Let suppose we are in March 2018 and the month is not finished then you need to compare your current data to the previous and current year month by month.

In the column "H'' I need to find a formula to sum only the previous month up to today.

And because March 2018 result isn't out yet then the calculation should stop at February for the row 2017 and 2018 as the example below

Hope you can help I can't find it.

Thank you in advance.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]year[/TD]
[TD]price 01[/TD]
[TD]volume01[/TD]
[TD]price 02[/TD]
[TD]volume 02[/TD]
[TD]price 03[/TD]
[TD]volume 03[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2018[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2018[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[JustynaMK]

Re: need help with sumifs with different column

Hi,

I'm sure there is a more elegant way of achieving this, but for now please try to use the following formula (cell H2 & drag it down):

=SUM(B2*(COUNTA($B:$B)=COUNTA(B:B)),D2*(COUNTA($B:$B)=COUNTA(D:D)),F2*(COUNTA($B:$B)=COUNTA(F:F)))

COUNTA check should determine if we are dealing with an incomplete month. In column F this statement will return 0, thus F2 value won't be included in the SUM calculation.

Let me know if that works for you.

 

[Nishant94]

Re: need help with sumifs with different column

Maybe something like this:

=SUMPRODUCT(SUMIF(B$1:G$1,"*price "&TEXT(ROW(A$1:INDEX(A:A,MONTH(NOW())-1)),"00"),B2:G2))

This formula its not assuming the current month to be March 2018. I have used now() assuming that you are looking for summing up data till current month. It will automatically change the month based on todays's date.

 

[teuteux13]

Re: need help with sumifs with different column

Dear Justyna,

It doesn't work all my result return always "0"

Can you simulate by copying my table and see if it is working for you

Thanks

 

[teuteux13]

Re: need help with sumifs with different column

Dear mr Nishant49,

Your formula doesn't work.. Only "0" as result

Could you try to simulate my table and make it work?

Thanks in advance

 

[JustynaMK]

Re: need help with sumifs with different column

Hi teuteux13,

I checked my code and Nishant's code and both seem to work fine. Is there any chance you can share a sample of your file (i.e. using online Excel document)?

Thanks,
Justyna

 

[teuteux13]

Dear Justyna,

Sorry i have double checked to an other laptop and it is working.

It's weird! it didn't work on my company laptop but work on my personal....

I will figure out tomorrow by reinstalling the software

Thanks and sorry for the callback

 

[JustynaMK]

No worries! Glad it works take care.