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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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?
 

Forum statistics

Threads
1,141,429
Messages
5,706,402
Members
421,447
Latest member
arthuro2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top