Excel 2007 : CSE Array Formula doesn't work

Hi All:

In the attached workbook I would like to sum the Hours ('Database'!Hours) expended in each Category ('Database'!Category) between the dates entered in cells 'Summary'!A1 and 'Summary'!B1. The data is located on a Database sheet and the formula on a Summary sheet (Summary!Hours). I am barred from using the SUMIFS function which would make life too easy. Naming the ranges didn't change anything so I am up a tree.

My CSE formula, for cell b4 on the Summary sheet is {=IF(AND(A4=Database!B:B,Database!A:A>=A1,Database!A:A<=B1),SUM(Database!C:C))} entered properly using Ctrl-Shift-Enter. The formula returns FALSE while I expect it to return 9 ... the number of hours in Category A between the dates of 1/5/2011 and 1/10/2011 inclusive.

Can anyone help?

Thanks for trying,

Stosh

Try:

=SUMIFS(Database!C:C,Database!B:B,A4,Database!A:A,">="&A$1,Database!A:A,"<="&B$1)

copied down

Why are you barred... what software are you using?

can you use helper columns?

Actually, the database is on Google Docs ... which does not support SUMIFS. So the gauntlet has been thrown! Can you do it without SUMIFS?

I can add other columns to either sheet.

There's Sumproduct:

=SUMPRODUCT(Database!C:C,--(Database!B:B=A4),--(Database!A:A>=A$1),--(Database!A:A<=B$1))

but it is not recommended to use whole columns with this function....

Originally Posted by NBVC

There's Sumproduct:

=SUMPRODUCT(Database!C:C,--(Database!B:B=A4),--(Database!A:A>=A$1),--(Database!A:A<=B$1))

but it is not recommended to use whole columns with this function....

Interesting approach and SUMPRODUCT is supported, however it returns an incorrect answer. Could it be multiplying (product?) instead of adding ?

I can't figure our why my original formula, {=IF(AND(A4=Database!B:B,Database!A:A>=A1,Database!A:A<=B1),SUM(Database!C:C))}, doesn't work ... it returns "FALSE".

BTW, I like your stylized Triceratops ... or is your avatar a different dinosaur?

If you want an array formula similar to what you have try:

=SUM(IF((A4=Database!B:B)*(Database!A:A>=A1)*(Database!A:A<=B1),(Database!C:C)))

confirmed with CTRL+SHIFT+ENTER or whatever google makes you do... again whole columns might not work with that either.


It is actually a Styrocasaurus.... my son is in to dinosaurs and knows quite a bit of them

It seems Google wants an ArrayFormula prefix

e.g.

=ArrayFormula(SUM(IF((A4=Database!B:B)*(Database!A:A>=A1)*(Database!A:A<=B1),(Database!C:C))))

I believe you can type in the ArrayFormula part or use CSE just like in Excel and it adds that (and the parentheses) automatically.

SUMPRODUCT works in Google docs but you can't use it for multiconditional counting as it's often used in Excel, only for manipulating numbers

Actually SUMPRODUCT might work ......

You have a strange date in B1 so you aren't restricting the date range as you want - change B1 to 1/10/2011