Add between dates and

[brandonmarketing7]

I have a problem, I created a formula to sum up items that are between dates: =(SUMIF(A:A,"<="&DATE(year(F1),month(F1),day(F1)),J:J)-SUMIF(A:A,"<"&DATE(year(C1),month(C1),day(C1)),J:J))
but say I have a list like:

7/1/12 bk 5
7/1/12 rk 3
7/2/12 bk 2
7/3/12 se 9

That formula will will to sum is everything is the same in a particular problem but if i have a list like that, what can I add onto the formula that will only sum the bk's that is between certain dates?

[brandonmarketing7]

I really need some help with this, any ideas that might work.

[DBY]

Hello
If for example your data was in the range A2:C5. Your From date in F2, your To date in G2 and finally your Code in H2, then the following formula should work:

=SUMPRODUCT((A2:A5>=F2)*(A2:A5<=G2)*(B2:B5=H2)*C2:C5)

Hope this helps.
DBY

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

Adapt ranges to your needs

[brandonmarketing7]

Here is a open doc link that I have the example: https://docs.google.com/spreadsheet/...1hjdmFkeW1KcWc at the top there is bk, rk, io, se and dates above that, i want to be able to insert different dates, and then for each of the 4 items have them total column C for the dates at the top.

[DBY]

Hello
I've recreated your Google Docs example in Excel. Take a look at the attachment. I've used Pepe's version of SUMPRODUCT with the double negatives (--) as it's more efficient if you're using a large data set. Note my dates are in the UK format you may have to change them.

DBY

[brandonmarketing7]

Thats great DBY, it works in excel, copy and pasting that into that google doc I get a parse error? You can edit that google doc if you like its open. google docs is all I use as I have a lot of people looking at reports. any idea why I am getting that error?

[Pepe Le Mokko]

Ask the people at Google

[DBY]

Hello
I've changed the formula in your Google Docs spreadsheet, it now seems to work. It doesn't accept the (--) and you have to wrap it in 'ArrrayFormula'. Took me awhile to figure it out, a bit of Googling was required.

DBY

[brandonmarketing7]

Thank you, that has taken me 5 months for me to even try to figure out it, you just saved me 2 hours a day, thank you.