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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You can use the sumproduct formula

Sumproduct((criteria 1)*(criteria 2)*(data to sum))
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
I'm trying to understand what your data looks like, but your fields aren't equal in terms of (Date) (ITEM) (Count) (Count)
 
Upvote 0
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!
 
Upvote 0
Also, do you mean for the last value in the C column to be 601 not 60?
 
Upvote 0

Forum statistics

Threads
1,219,101
Messages
6,146,288
Members
450,685
Latest member
frederik00

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
Back
Top