# SUMIF with multiple criteria

#### Nomis_Eswod

##### Board Regular
Hi,

I have a spreadsheet, with 2 columns that I'm trying to SUMIF. Column F is amount, and column G is date. I need to SUM column F if the date in column G falls between 2 dates, say 1/1/05 and 31/3/05.

Any ideas on how I can do this??

It would also be good to have the criteria as cell references, so that if I need to change either of the dates, the SUMIF will be calculated automatically.

Any help appreciated!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Assume lower criteria date is in I1, higher criteria date is in J1.

Try
Code:
``=SUMPRODUCT(--(\$G\$1:\$G\$100>I1),--(\$G\$1:\$G\$100<J1),\$F\$1:\$F\$100)``
Adjust ranges to suit but they must all be the same size and cannot be complete columns.

Hi,

=SUMIF(G1:G26,">"&I1,F1:F26)-SUMIF(G1:G26,">"&I2,F1:F26)

where I1 - 1/1/05 and
I2 - 31/3/05

HTH

That's great - thanks!

Any chance you can break it down? E.g. what the -- means? I did look at SUMPRODUCT but couldn't work out how to get it to evaluate something.

Nomis_Eswod said:
That's great - thanks!

Any chance you can break it down? E.g. what the -- means? I did look at SUMPRODUCT but couldn't work out how to get it to evaluate something.
SUMPRODUCT multiplies ranges together and adds the results, so the ranges need to be numbers. \$G\$1:\$G\$100>I1 returns a series of TRUEs and FALSEs -- (which is just 2 minus signs) converts TRUEs to 1s and FALSEs to 0s

That's all I have time for now, but if you want a fuller explanation of SUMPRODUCT and --, do a search of this board as the question has been asked many times. Look for answers by Aladin Akyurek as he has given a number of very informative explanations about this.

Replies
17
Views
344
Replies
26
Views
1K
Replies
6
Views
292
Replies
14
Views
276
Replies
3
Views
126

1,214,498
Messages
6,119,892
Members
448,929
Latest member
Giovannicavuccio

### 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