Google Docs - Alternative to SUMIFS

[jayooo]

Hi all, this is my first post, and I would be very grateful if anyone can help me solve this issue.

I have set up a spreadsheet in Excel 2010 that is fully working, but I am looking to migrate to Google docs so I can have a live update on Ipad and PC.

I am aware SUMIFS does not work on Google docs, and I think SUMPRODUCT is what I need to change it to, however I cannot get it to work.

The formula I have in Excel is =SUMIFS(Income!$G:$G,Income!$B:$B,">=01/04/2013",Income!$B:$B,"<=30/4/2013") where G is the amount in £, and B is a specified date. I am essentially looking to total monthly income, and I will have a similar formula for expenditure on a different sheet.

If anyone can shed some light on an alternative formula that is compatible with Google Docs, that would be brilliant.

Thanks

James

[Fotis1991]

=SUMPRODUCT((Income!$B2:$B5000>=f1)*Income!$B2:$B5000<=e1)*(Income!$G2:$G5000))

Perhaps something like this? F1=start date E1= End Date

[Pete_UK]

@Fotis:

missing bracket:

=SUMPRODUCT((Income!$B2:$B5000>=f1)*(Income!$B2:$B5000<=e1)*(Income!$G2:$G5000))

Pete

[jayooo]

Thank you both for the help!

The formula gives me £0.00 which would imply that it works, which is further than I got as I was getting #error or #value.

I have dates in column B, and takings in column G, but the formula is still giving me £0.00.

Using April as an example I have =SUMPRODUCT((Income!$B2:$B5000>=01/04/2013)*(Income!$B2:$B5000<=30/4/2013)*(Income!$G2:$G5000))

In column B I have 15/04/2013 and in column G i have £15.00. Column B is formatted as a date.

Is this something to do with Google having the day and month the other way round like the USA? I cant see any reason why this formula shouldn't return the £15.00.

Thanks

James

[Fotis1991]

Thanks Pete!

James i don't use google docs so i really don't know what happens...As an idea use cell reference for you dates as i suggested and see if something happens...

[jayooo]

Thanks anyway Fotis, I have tried with cell reference too but Google docs seems not to like that also

Thank you very much for your help anyway!

James

[JosephP]

if your dates are stored as dates (they ought to be right aligned in the cells) then perhaps

=SUM(FILTER(Income!$G2:$G5000,Income!$B2:$B5000>=date(2013,4,1),Income!$B2:$B5000<=date(2013,4,30)))

[Pete_UK]

See DDL's post #11 in this thread:

http://www.excelforum.com/for-other-...-countifs.html

That is about COUNTIFs, but if you add a term to sum, i.e. Income!$G2:$G5000, then you will get the equivalent to SUMIFs.

Hope this helps.

Pete