# Sumif With 2 criteria

#### simonw

##### New Member
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.
Hi:

Have you ruled out the pivot table approach?

plettieri

You can use the sumproduct formula

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

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

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

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

What formula did you try?

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.

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

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!

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

Replies
3
Views
189
Replies
3
Views
306
Replies
3
Views
485
Replies
7
Views
211
Replies
1
Views
177

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.

### Which adblocker are you using?

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

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