Sumifs with SAPMember cells

derica

New Member
Joined
Dec 18, 2017
Messages
15
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
 
=SUMPRODUCT(--('OB YTD'!B2:B1000=C69),--(--'OB YTD'!G2:G1000>C68),'OB YTD'!AD2:AD1000)

Updated to use your list separator.

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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi,

hopefully the holidays were nice. :)

Unfortunately that didnt work either, gave the value 0. Have you any other ideas?

Thank you a lot!
 
Upvote 0
hopefully the holidays were nice. :)

Hi, they were thanks :) hope they were for you also.

Unfortunately that didnt work either, gave the value 0.

Maybe the values you are trying to sum are also text that happens to look like numbers. To check, if you put =ISNUMBER(AD2) in the OB YTD sheet and copy down to all rows, do you get any FALSE values returned?
 
Upvote 0
What do you have in cell C69 and what is in column B of the OB YTD sheet? Are you sure they exactly match for the rows you expect to get summed? You can test with:

='OB YTD'!B2=C69

After changing the row number to one you expect to match.
 
Upvote 0
There is another code and Yes, that was true also! I think the problem is with the dates..

I also tried the first suggested formula

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

and changed the C68 cell also to be "SAP member cell" but that didint work either... Below is the formula that works but as said, I dont want to change the date in formula every month :)

=SUMIFS('OB YTD'!$AD:$AD;'OB YTD'!$G:$G;">'30.11.2017";'OB YTD'!$B:$B;$C$69)
 
Upvote 0
Hey, I made this same for the date cell,

=C68='OB YTD'!G180

and found cell that should be matching but it gives FALSE. So the problem really is with dates...
 
Upvote 0
Both TRUE..

I'm confused, so all of these return TRUE but the SUMPRODUCT() formula returns zero?

=C68='OB YTD'!G180
=--'OB YTD'!G180>C68
=ISNUMBER('OB YTD'!G180)

Can you remove all sensitive data and put a copy of the workbook on a file sharing site (like dropbox) and share the link here so we can take a look at it?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top