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

 

[derica]

I will make a file that I can share

 

[derica]

Hi again,

I made and example file and the SUMPRODUCT worked, and now I can see that the problem is that in my data sheet, there are couple days that are # and therefore the formula does not give any values.

I dont want to change or delete these figures, so what kind of formula would you suggest?

Thank you for your help! Super helpful!

 

[FormR]

the problem is that in my data sheet, there are couple days that are # and therefore the formula does not give any values.

Hi, I'm sorry - I don't understand what you mean? What is in the cells that relate to these "couple of days", what column are they and how do they prevent the suggested formula from working?

 

[derica]

Hi,

sorry for not being clear. So in column G we have the dates that we are referring: =SUMPRODUCT(--('OB YTD'!B3:B405=C69);--(--'OB YTD'!G3:G405>C68);'OB YTD'!AD3:AD405)

G-column:

01.10.2016

01.10.2016

#

27.02.2018

<colgroup><col></colgroup><tbody>
</tbody>

And the formula only works if all the dates are in date format. Now when one of the days is "#" formula gives "#VALUE!".

Is there any way around? Thank you a lot!

 

[FormR]

And the formula only works if all the dates are in date format. Now when one of the days is "#" formula gives "#VALUE!".

that didnt work either, gave the value 0.

Hi, so the formula is no longer returning the value of zero

Is there any way around?

Assuming it's only text dates, blanks and # in the column, you can try:

=SUMPRODUCT(--('OB YTD'!B3:B405=C69);--(--TRIM(SUBSTITUTE(G3:G405&" 0:";"# ";"0:"))>C68);'OB YTD'!AD3:AD405)

 

[derica]

We are getting there...

Now the formula is returning #VALUE !, but if I put

=(SUMPRODUCT(--('OB YTD'!B3:B405=C69);--(--TRIM(SUBSTITUTE('OB YTD'!G3:G405;"#";"0:"))>C68);'OB YTD'!AD3:AD405)) it gives the right value!!

Now only problem is, that with this data we dont have blanks, and if we would, it gives #VALUE !

For example this gives #VALUE !:

=(SUMPRODUCT(--('OB YTD'!B3:B1000=C69);--(--TRIM(SUBSTITUTE('OB YTD'!G3:G1000;"#";"0:"))>C68);'OB YTD'!AD3:AD1000)) because the cells after row 405 are blanks (in this example)

 

[FormR]

I see now that I missed the sheet reference, it should have been.

=SUMPRODUCT(--('OB YTD'!B3:B405=C69);--(--TRIM(SUBSTITUTE('OB YTD'!G3:G405&" 0:";"# ";"0:"))>C68);'OB YTD'!AD3:AD405)

 

[derica]

WORKS!!!

Amazing, thank you for all your efforts!

 

[FormR]

WORKS!!!

Phew!