Sumifs with SAPMember cells

[derica]

Hi,

Thanks for great support beforehand!

I am trying to do sumifs with two criteria and the other one is causing problems. My criteria range cells are in 'date format as in '2.12.2017.

How to refer to this kind of cells?

This is working as I write the criteria date to the formula, but I need it to be formula too. How to do it?

Works:

=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">'30.11.2017";'OB YTD'!$B:$B;$C$69)

Wont work:
=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">"&"C68";'OB YTD'!$B:$B;$C$69)
=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">'&"C68";'OB YTD'!$B:$B;$C$69)
=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">"&"'"&"C68";'OB YTD'!$B:$B;$C$69)
=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">(EOMONTH(TODAY();-1))";'OB YTD'!$B:$B;$C$69)
=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">'(EOMONTH(TODAY();-1))";'OB YTD'!$B:$B;$C$69)

Thanks for any support!

-Erica

 

[FormR]

Hi, welcome to the board!

You could give this a try:

=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">" & C68;'OB YTD'!$B:$B;$C$69)

 

[derica]

Hi, thanks!

Unfortunately that does not work either. Thank you anyway!

 

[FormR]

Hi, it is the correct syntax.

What exactly do you have in cell C68 and are your dates in column G real Excel dates, or text that happens to look like dates?

 

[derica]

Hey, ok lets try to find the reason why it does not work

In C68 I have the day formula: =EOMONTH(TODAY();-1)

In Column G cells are general and Style "SAPMemberCell". This data comes straight from Analysis plug-in so I dont want to change it (as its refreshed every month)
Looks 18.06.2018 but when pressing the cell it shows '18.06.2018

Thank you very much for any help!

 

[FormR]

Looks 18.06.2018 but when pressing the cell it shows '18.06.2018

It would appear that those are text, it would make you life much easier if you can convert them to proper dates. You could maybe use a formula in a helper column to do that. Is this something you can do/would like to explore?

 

[derica]

Unfortunately not - I am making a tool and because the data is refreshed monthly, we cant ask users to always change those.

Is there any other options? As you see in my question, there is one workaround but I would not like to use that as it also needs updating monthly..

Thank you beforehand!

 

[FormR]

Is there any other options?

Yes, I'm sure - a few questions.

1. Does your data always consist of just the current year?
2. Are your dates dd.mm.yyyy or mm.dd.yyyy
3. If you put this formula into a spare cell on the "OB YTD" sheet =G2+0 and change to G2 to a cell that contains one of your dates what does it return?

 

[derica]

Hi,

1. Does your data always consist of just the current year?
Date data in SAPMemberCells can be anything and will be refreshed monhly for example 07.12.2014 or 09.12.2019

2. Are your dates dd.mm.yyyy or mm.dd.yyyy
dd.mm.yyyy

3. If you put this formula into a spare cell on the "OB YTD" sheet =G2+0 and change to G2 to a cell that contains one of your dates what does it return?

Did this to cell that has '05.11.2018 and returns 43409

Thank you so much for the help!

 

[FormR]

Did this to cell that has '05.11.2018 and returns 43409

Great, you can give this a try, note - you should avoid using full column references with this type of formula and you should not include the header row if there is one.

=SUMPRODUCT(--('OB YTD'!B2:B1000=C69),--(--'OB YTD'!G2:G1000>C68),'OB YTD'!AD2:AD1000)