Alternative to SUMPRODUCT for Documents to Go

[Metavanguard]

I have a SUMPRODUCT formula that isn't compatible with Documents to Go on my mobile devices. I have tried rewriting with SUMIF but I keep getting stuck on entering a cell reference into SUMIF. Maybe SUMIF can't take a cell reference with operands?


Here is the formula that I'm trying to simplify from Excel 2003:


D2 is a date

E2 is a number to calculate weeks: (7*E2-1) is E2 number of weeks minus 1 day

I4:I500 are dates

O4:O500 are dollars


=SUMPRODUCT((I4:I500>=(D2-(7*E2-1)))*(I4:I500<=D2)*(O4:O500))


What I've been trying with SUMIF is:


=SUMIF(I4:I500,"<=D2",O4:O500)-SUMIF(I4:I500,"<=D2-(7*E2-1)",O4:O500)


When I replace D2 with an actual date it works.

=SUMIF(I4:I500,"<=2012-12-12",O4:O500)-SUMIF(I4:I500,"<=2012-11-12",O4:O500)


Any help would be appreciated.

 

[sgmpatnaik]

@Metavanguard


Hi


Firstly welcome to Chandorg forums, glad you are here


is it possible to upload a sample work book


if you don't how upload the sample workbook please read the 4 green sticky key it will help you how to upload a sample work book


Thanks


SP

 

[sgmpatnaik]

@Metavanguard


HI


Firstly welcome to Chandoo.org Forums, glad you are here


is it possible to upload a sample work book


Thanks


SP

 

[sgmpatnaik]

@Metavanguard


Hi


Firlstly welcome to chanddo.org forum


can you upload a sample workbook


Thanks


SP

 

[Metavanguard]

Here is the link to the sample.xls


https://www.box.com/s/tnclny4wg5bxhy2j1944

 

[NARAYANK991]

Hi ,


I am not very sure about your problem , but one possibility is that the criteria is supposed to be either a numeric value alone or a string.


When you use a specific value , you can include it within quotes , but when you use a cell reference , you cannot include it within the quotes ; what you need to do is concatenate it with the comparison operators i.e. = , > , >= , < , <= , <>.


For example , if you want the criterion to be greater than or equal to D2 , it has to be specified as ">="&D2 ; your formula will be :


=SUMIF(I4:I500,"<="&D2,O4:O500)-SUMIF(I4:I500,"<="&D2-(7*E2-1),O4:O500)


Narayan

 

[Metavanguard]

Thank you so much for the tip! I reworked the formula and it worked!


=SUMIF(I4:I500,">="&(D2-(7*E2-1)),O4:O500)-SUMIF(I4:I500,">="&D2,O4:O500)


Now I have to convert all the SUMPRODUCTS to SUMIF then I will test it on my mobile devices! Thanks again to Chandoo and friends!