Sumif Dates excluding some

[mickeystanford_alumni]

Hi everyone,

I am trying to sum various columns with values where the first column is a date column. However, what I want is to exclude certain value linked to certain dates. E.g.

A1 B1 C1
01/01/1990 50 50
02/01/1990 60 100
03/01/1990 70 10
04/01/1990 80 50
05/01/1990 90 70

I want to sum B1 and C1, but excluding for example 03/01/1990.

For simplication this is the example, however, there are like 20 dates I would like to exclude. So not sure if making an index in a way where I could choose all dates I want to exclude would make it easier. As if in the formula I have to choose one by one is a very very long formula.

Thank you.

M

 

[jasonb75]

The first two methods that come to mind. It won't make much difference with the example but you might find that one calculates faster than the other with a large data set.

Book1
	A	B	C	D	E
1					Exclude
2	01/01/1990	50	50		03/01/1990
3	02/01/1990	60	100		04/01/1990
4	03/01/1990	70	10
5	04/01/1990	80	50
6	05/01/1990	90	70		420
7					420
Sheet4

Cell Formulas
Range	Formula
E6	E6	=SUM(FILTER(B2:C6,ISNA(MATCH(A2:A6,E2:E3,0)),0))
E7	E7	=SUM(B2:C6,-SUMIF(A2:A6,E2:E3,B2:B6),-SUMIF(A2:A6,E2:E3,C2:C6))

 

[Legacy 143009]

Let say you have list of will be excluded dates on column D. D1: 2.01.1990, D2: 3.01.1990. B6 is going to give you 220 excluding these dates.

=SUM(B1:B5)-SUMPRODUCT(SUMIF($A1:$A5,$D1:$D2,B1:B5))

 

[mickeystanford_alumni]

Hi both, thank you so much. Both methods perfectly work.
One more question is, what about if I would like to do the opposite, from the A1 column, only add the excluding ones.
It could possibly be done with =SUM(B2:C6)-=SUM(FILTER(B2:C6,ISNA(MATCH(A2:A6,E2:E3,0)),0)).
But maybe there is other way with the filter function...just wondering.
Thanks a lot again

 

[Legacy 143009]

For my solution it is:

=SUMPRODUCT(SUMIF($A1:$A5,$D1:$D2,B1:B5))

 

[jasonb75]

It could possibly be done with =SUM(B2:C6)-=SUM(FILTER(B2:C6,ISNA(MATCH(A2:A6,E2:E3,0)),0)).

You don't need the whole thing, just

=SUM(B2:C6)-E1

Where E1 contains the other formula that excludes the unwanted dates.

 

[mickeystanford_alumni]

Thank you guys. Appreciate it.

 

[mickeystanford_alumni]

Hello, could I please ask something else?
If I have the dates in the format dd/mm/yyyy hh:mm, and I would like to just sum the value between 10:00-11:00?
Let say in the dataset by Jason:
01/01/1990 9:00 10
01/01/1990 10:00 20
01/01/1990 11:00 10
01/01/1990 12:00 15

Therefore, I would like to get the values between 10:00 and 11:00, which is 30. Again, in a way which I scale this to many dates, although the time constraint will be always the same.
Thank you so much for your patience and aid.

 

[jasonb75]

Would it just be one column of values this time or still 2 columns as with the original question?

With 1 column I would use a variation of the sumif method but with more i think that filtering using mod would be more efficient.

 

[mickeystanford_alumni]

Hey Jason, thank you for your response.
It would be 2 columns still. Same as original question but with date+time as I previously commented.
Thank you for your help