Sumifs not working

[deckerp]

In the first sheet there is a table with many rows containing dates and amounts received. The dates and amounts received are from another workbook
=IFNA(INDEX('[Zahlungseingänge.xlsx]Worksheet'!$A$1:$O$65536;MATCH($B7;'[Zahlungseingänge.xlsx]Worksheet'!$A$1:$A$65536;0);10);"")

In the second sheet I would to sum the amounts per month. For this, I use again sumifs function, this time referring to the first sheet
=SUMIFS(Zahlungen[Betrag];Zahlungen[Zahlung am];">"&EOMONTH($B9;-1);Zahlungen[Zahlung am];"<="&$B9)

For some reason, the formula on the second sheet only works in case I copy values only into the first sheet.
Also some strange formatting issue: The amount in the first sheet cannot be shown as "1,00 €" but is shown as "1,00" only without the currency symbol.

Your solution to the problem is very much appreciated.

 

[RoryA]

That suggests that the amounts are actually text, not proper numbers. You could try using:

=IFNA(--INDEX('[Zahlungseingänge.xlsx]Worksheet'!$A$1:$O$65536;MATCH($B7;'[Zahlungseingänge.xlsx]Worksheet'!$A$1:$A$65536;0);10);"")

 

[deckerp]

That suggests that the amounts are actually text, not proper numbers. You could try using:

=IFNA(--INDEX('[Zahlungseingänge.xlsx]Worksheet'!$A$1:$O$65536;MATCH($B7;'[Zahlungseingänge.xlsx]Worksheet'!$A$1:$A$65536;0);10);"")

Hello Rory, thanks for looking into this thread.
I think you wrote the same formula as I did above. The formula in the first sheet works fine. I need help with the second one.Thank you.

 

[RoryA]

I think you wrote the same formula as I did above

No, I didn't. Note the -- before the INDEX

 

[deckerp]

No, I didn't. Note the -- before the INDEX

Hi Rory, if I put this into the first formula in the first worksheet (which is working fine and pulling correct data) with
IFNA(--INDEX ...
the formula produces an error #VALUE!

My issue seems to be the second formula. Or I misunderstand something.

 

[RoryA]

No, that means that whatever is being returned by your INDEX formula cannot be converted to a number value (which is why you were getting 0 as the result of your SUMIFS).

 

[deckerp]

wow - I did not know this and needed two rounds to understand what you meant....
Now it is working fine. I needed to adjust the format of the original data sheet and everything works now.

Many thanks for your great help - solved. You made my day

 

[RoryA]

Glad we could help.