#Ref! error help

[razaas]

Hi, I am trying use the below formula but it is returning #Ref! error, when I try SUMIFS separately and SUMPRODUCT separately they both returns correct result and also the complete formula with F9 showing the correct values.

INDEX(SUMIFS('Daily Income Record'!$C$4:$C$1514, 'Daily Income Record'!$B$4:$B$1514, ">="&EOMONTH('Daily Income Record'!$B4,-1)+1, 'Daily Income Record'!$B$4:$B$1514, "<="&EOMONTH('Daily Income Record'!$B4,0)),SUMPRODUCT(MAX(('Daily Income Record'!$B$4:$B$1514=EOMONTH('Daily Income Record'!$B4,0))*ROW('Daily Income Record'!$B$4:$B$1514))))

 

[Debaser]

Your formula makes no sense. Your SUMIFS returns one value, not an array, so what are you trying to achieve with INDEX?

 

[razaas]

Your formula makes no sense. Your SUMIFS returns one value, not an array, so what are you trying to achieve with INDEX?

Thanks for reply, I have more than one 'end of month' dates in a column, I want end of month total only once at the last occurrence of end of month.

 

[pecoflyer]

Sample sheet please ( no pics) with some manually mocked up results. Thx

 

[razaas]

Sample sheet please ( no pics) with some manually mocked up results. Thx

Thanks for the reply, sample file attached, I want the result (total) at end of month but at the last occurrence of each end of month date, not repeated as many times as the date repeated.

 

[pecoflyer]

In D4 and pulled down

=IF(MONTH(B5)<>MONTH(B4),SUMPRODUCT(--(MONTH($B$4:$B$32)=MONTH(B4))*($C$4:$C$32)),"-")

 

[razaas]

In D4 and pulled down

=IF(MONTH(B5)<>MONTH(B4),SUMPRODUCT(--(MONTH($B$4:$B$32)=MONTH(B4))*($C$4:$C$32)),"-")

Thanks for the reply, but your formula checking only month, if the year changes then it is not matching the date, could you please provide solution by evaluating the full date including year.

 

[vletm]

razaas​

One sample solution ...
You should able to show - what do You need?
instead that,
You're showing - what do You have?

 

[razaas]

razaas​

One sample solution ...
You should able to show - what do You need?
instead that,
You're showing - what do You have?

Thanks for the reply, I thought my explanation was clear enough with the sample and 'pecoflyer' got it right. I was looking for the result to be within the same table not as a pivot table, anyway no problem thanks once again.

 

[vletm]

razaas​

Did You show somewhere - what do You need?
... eg I was looking for the result to be within the same table not as a pivot table
... if so, then where?
Did You ask something else after #6 reply?
Did You ask Could you please provide solution by evaluating the full date including year. ?

 

[razaas]

Did You ask something else after #6 reply?
Did You ask Could you please provide solution by evaluating the full date including year. ?

Yes I did, please refer #7.

 

[razaas]

razaas​

Did You show somewhere - what do You need?
... eg I was looking for the result to be within the same table not as a pivot table
... if so, then where?
Did You ask something else after #6 reply?
Did You ask Could you please provide solution by evaluating the full date including year. ?

#5 and #7 both are clear about what I need, anyway no issues, I didn't mean to discredit your solution, though it is not what I need but I am really ok with it and can close the thread as solved. Thanks

 

[pecoflyer]

Thanks for the reply, but your formula checking only month, if the year changes then it is not matching the date, could you please provide solution by evaluating the full date including year.

I added two rows with different years and it still works perfectly, so where is the problem?

 

[razaas]

I added two rows with different years and it still works perfectly, so where is the problem?

Yes it is working perfectly, thanks a lot and very sorry I didn't check it properly earlier. Thanks

 

[vletm]

razaas​

Did You really check it properly?
I modified the last Date's year to 2023.

... then Months Nov-2022 is same as Nov-2023.
Do Nov-2022 need to have something End of Month value?

 

[razaas]

razaas​

Did You really check it properly?
I modified the last Date's year to 2023.
View attachment 88323
... then Months Nov-2022 is same as Nov-2023.
Do Nov-2022 need to have something End of Month value?

Yes I checked it, it is working but require to drag down the formula from cell above, if only the cell value changed it won't update automatically. Thanks for follow up.

 

[vletm]

razaas​

You skipped my point.
As in tha snapshot Nov 30 2022 and Nov 30 2023
Months are same ... but years are different.
As I asked: Do Nov-2022 need to have something End of Month value?
Of course, those Dates should be sorted as needed, if use some formulas.

 

[razaas]

razaas​

You skipped my point.
As in tha snapshot Nov 30 2022 and Nov 30 2023
Months are same ... but years are different.
As I asked: Do Nov-2022 need to have something End of Month value?
Of course, those Dates should be sorted as needed, if use some formulas.

Yes last occurrence of each end of month date, including year, should have end of month total for that particular month and year. Thanks

 

[p45cal]

I can't test this at the moment but maybe try changing all instances of MONTH in pecoflyer's formula in msg#6 to EOMONTH ?

 

[razaas]

I can't test this at the moment but maybe try changing all instances of MONTH in pecoflyer's formula in msg#6 to EOMONTH ?

Thanks for the reply, no it is not working, it returns same result with EOMONTH also, sums together each month from different years, such as Nov. 2022 and 2023 together and so on.

 

[p45cal]

Thanks for the reply, no it is not working, it returns same result with EOMONTH also, sums together each month from different years, such as Nov. 2022 and 2023 together and so on.

Still can't test but you replaced ALL (4) instances of MONTH in the formula?

 

[razaas]

Still can't test but you replaced ALL (4) instances of MONTH in the formula?

Yes!

 

[p45cal]

Having got myself in front of a computer I see now that I forgot (a) that EOMONTH needs 2 arguments and (b) that EOMONTH won't work as MONTH does with arrays.
So, half tested:

=IF(EOMONTH(B5,0)<>EOMONTH(B4,0),SUMPRODUCT((MONTH($B$4:$B$33)=MONTH(B4))*(YEAR($B$4:$B$33)=YEAR(B4))*($C$4:$C$33)),"-")

 

[razaas]

Having got myself in front of a computer I see now that I forgot (a) that EOMONTH needs 2 arguments and (b) that EOMONTH won't work as MONTH does with arrays.
So, half tested:

=IF(EOMONTH(B5,0)<>EOMONTH(B4,0),SUMPRODUCT((MONTH($B$4:$B$33)=MONTH(B4))*(YEAR($B$4:$B$33)=YEAR(B4))*($C$4:$C$33)),"-")

Thanks a lot, it works perfectly. Highly appreciate.