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]

Sorry, 1 last question. Will the times always be on the hour (10:00, 11:00, etc) or will the actual times be somewhere between the 2?

I've tried several methods that should work but they are not picking up the correct results. Due to the way that excel handles times 10:00 as a time in cell does not match correctly to the same time in a formula

I'm going to keep looking at it but so far I've had about 10 different failures.

 

[mickeystanford_alumni]

Sorry, 1 last question. Will the times always be on the hour (10:00, 11:00, etc) or will the actual times be somewhere between the 2?
--> It will be always be in this format.

Its very tricky with this date+time format. What I did is to convert text to columns, so that I divide the date and time in a second column. However, with the filtering formula you sent, not really able to sum the "in between" two hours. So from 10:00-11:00.

Hopw the idea of text to columns makes it easier...tks sir

 

[jasonb75]

Using text to columns, or a helper formula to split the date and time was something that crossed my mind, in all honesty it doesn't make any difference to the results, but it does make the formulas shorter and easier to write, although there is very little difference with this one. The formula below will work with the dates and times either in the same column, or split into separate columns.

If they are separate columns then the ranges in both HOUR() functions need to be changed to look at the time column instead of the dates.

Book1
	A	B	C	D	E	F
1						Exclude
2	01/01/1990 09:00	09:00:00	3	31		02/01/1990
3	01/01/1990 10:00	10:00:00	68	18		03/01/1990
4	01/01/1990 11:00	11:00:00	62	8
5	01/01/1990 12:00	12:00:00	80	62
6	01/01/1990 13:00	13:00:00	43	56		156
7	02/01/1990 09:00	09:00:00	16	37
8	02/01/1990 10:00	10:00:00	2	6
9	02/01/1990 11:00	11:00:00	58	12
10	02/01/1990 12:00	12:00:00	13	91
11	02/01/1990 13:00	13:00:00	17	72
Sheet1

Cell Formulas
Range	Formula
F6	F6	=SUM(C2:D11*ISNA(MATCH(INT(A2:A11),F2:F3,0))*((HOUR(A2:A11)=10)+(HOUR(A2:A11)=11)))

 

[mickeystanford_alumni]

Terrific. Thank you very much gent.