Sumif With 2 criteria

[simonw]

Hi:
I have a table of increasing data, the volume of daily data is variable; and I would like to summarise the data by date by area only.
As you select different dates the summary evaluates the data to compile the daily summary. In operation the date function will be Today()-1, therefore always giving yesterdays date and data.

Using the formula {=SUM((A8:A60=D9)*(B8:B60=E8)*C8:C601)}, when I try to extend the range past row 65 a #VALUE! expression is given.

Is there something obvious I have not seen or is this formula just not suitable?
Can you recommend a more suitable solution?
I would prefer not to use macros, as this sheet will form via links part of a larger report.



Date Area Total
03/09/06 Coating 3 300
04/09/06 Coating 3 300
04/09/06 Coating 3 150
05/09/06 Jet Zone 600
05/09/06 Jet Zone 150
07/09/06 RiceCooking 55
07/09/06 RiceCooking 600
08/09/06 Coating 3 75
08/09/06 Coating 3 75
09/09/06 Coating 3 150
09/09/06 Coating 3 150
11/09/06 RiceCooking 275
11/09/06 RiceCooking 28
11/09/06 Jet Zone 300
11/09/06 RiceCooking 450
11/09/06 RiceCooking 300
11/09/06 Coating 3 600



Date Coating 2 Coating 3 Jet Zone RiceCooking
11/09/06 0 600 300 1,053

 

[plettieri]

Hi:

Have you ruled out the pivot table approach?

plettieri

 

[Legacy 51064]

You can use the sumproduct formula

Sumproduct((criteria 1)*(criteria 2)*(data to sum))

 

[simonw]

I have it working using pivot table and showed the end users, but they like the linked method ie no input just read

I have also tried SUMPRODUCT and get the #VALUE! expression after 65 rows

simonw

 

[Superstar31]

Did you try the sumproduct method yet? Any other questions?

Dont just edit your post, reply, otherwise we don't know if you answered the question

 

[barry houdini]

What's in row 65? Do you have an error in that row?

What formula did you try?

 

[simonw]

There are no error after row 65 it is just as far as I can extend the range without getting the #VALUE! expression

I have used the original and the SUMPRODUCT both fail, I have even made a completly new sheet using different data, but when you extend the range they fail.

 

[Superstar31]

I'm trying to understand what your data looks like, but your fields aren't equal in terms of (Date) (ITEM) (Count) (Count)

 

[orysiar]

Hi,

Did you absolute the range of cells? This may be the reason you can't get past row 65.

For example, instead of A8:A60 -> $A$8:$A$60 - this will need to be done for all of your ranges if the reference is the same throughout. If you only want to absolute the column, then it would appear like A$8:A$60 vs. a row being $A8:$A60.
{=SUM((A8:A60=D9)*(B8:B60=E8)*C8:C601)}, when I try to extend the range past row 65 a #VALUE! expression is given.

Good luck!

 

[Scott Huish]

Also, do you mean for the last value in the C column to be 601 not 60?