Adding 2 products

Hi

...I wish to include "Electricity" and one other.

The other one is in the same column as Electricity? or in another column?

=SUMPRODUCT(--(Expenses!A1:A590>=DATE(2013,4,6)),--(Expenses!A1:A590<=DATE(2013,4,30)),--(Expenses!F1:F590="Electricity")+(Expenses!F1:F590="something else"),Expenses!D1:D590)

Since you are using Excel 2007 use sumifs instead of sumproduct....

Originally Posted by :) Sixthsense :)

Since you are using Excel 2007 use sumifs instead of sumproduct....

Does SUMIFS bring some added value compared to SUMPRODUCT ?

Originally Posted by Pepe Le Mokko

Does SUMIFS bring some added value compared to SUMPRODUCT ?

Yes... It runs faster than sumproduct

you cant just use sumifs with OR you need to sum the sumifs
=SUM(SUMIFS(expenses!D1:D590,expenses!A1:A590,">="&DATE(2013,4,6),expenses!A1:A590,"<="&DATE(2013,4,30),expenses!F1:F590,{"electricity","something else"}))

Originally Posted by martindwilson

you cant just use sumifs with OR you need to sum the sumifs

Of course because of using criteria values in array... otherwise no need for it

=SUMIFS(expenses!D1:D590,expenses!A1:A590,">="&DATE(2013,4,6),expenses!A1:A590,"<="&DATE(2013,4,30),expenses!F1:F590,"electricity",expenses!F1:F590,"something else") doesnt work

Originally Posted by martindwilson

=SUMIFS(expenses!D1:D590,expenses!A1:A590,">="&DATE(2013,4,6),expenses!A1:A590,"<="&DATE(2013,4,30),expenses!F1:F590,"electricity",expenses!F1:F590,"something else") doesnt work

Yes surely it won't work because the condition is considered as AND Operation on the same range. As you are aware there will not be any possibility/probability which cannot occur because cell can't have Two Complete text on the same cell.

Again you are very much aware that using the criteria within array {} is considered as OR but the one which you shown which operates as AND

So it is not the right example to discuss I believe

well what do you think it should be?

Assume that the cell entries are like this
electricity and something else
gas
electricity and something else
electricity

By using the formula in this way which will get the expected result
=SUMIFS(expenses!D1:D590,expenses!A1:A590,">="&DATE(2013,4,6),expenses!A1:A590,"<="&DATE(2013,4,30),expenses!F1:F590,"*electricity*",expenses!F1:F590,"*something else*")

I just wanted to confirm you that the formula posted in Post #9 (In the body of the post) is not the right method of usage because Two Complete text cannot be matched on single range.

that doesnt work if used in my attached workbook it gives 0

ah an assumption ... electricity and something else in the same cell,i read it as electricity OR something else in each cell
the op needs to specify what's in each cell

pogo.stix attach a sample workbook

hmm you can always make a mock up with fictitious data anyway we only need to see a column with dates a column with text and a column to sum ie the columns you need to use in the formula

Originally Posted by martindwilson

ah an assumption ... electricity and something else in the same cell,i read it as electricity OR something else in each cell

Thanks for the understanding