Sumif With 2 criteria

simonw

New Member
Joined
Mar 5, 2004
Messages
32
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

Have you ruled out the pivot table approach?

plettieri
 
L

Legacy 51064

Guest
You can use the sumproduct formula

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

simonw

New Member
Joined
Mar 5, 2004
Messages
32
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

Active Member
Joined
Nov 10, 2005
Messages
496

ADVERTISEMENT

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 :)
 

simonw

New Member
Joined
Mar 5, 2004
Messages
32

ADVERTISEMENT

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

Active Member
Joined
Nov 10, 2005
Messages
496
I'm trying to understand what your data looks like, but your fields aren't equal in terms of (Date) (ITEM) (Count) (Count)
 

orysiar

New Member
Joined
Sep 14, 2006
Messages
13
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Also, do you mean for the last value in the C column to be 601 not 60?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top