"SUMIFS returning Zero"

[Marwan69]

Hello everyone,

I have data consists of dates and values. Dates start from column A1 to A9. Values start from B1 to B9. They are as follows:

Dates Values

01/01/2013	5,200
02/01/2013	2,500
03/01/2013	7,846
04/01/2013	444
05/01/2013	5,852
06/01/2013	441
07/01/2013	696
08/01/2013	332
09/01/2013	114

<colgroup><col><col></colgroup><tbody>
</tbody>

The formula I'm using is: SUMIFS(B1:B9,A1:A9,A1,A1:A9,A7)

This formula returns zero value. Any suggestions?

Many thanks,
Marwan

 

[RoryA]

The value in column A can't be both A1 and A7, since they are different. If you wanted the sum for dates between those dates, you'd need:

=SUMIFS(B1:B9,A1:A9,">="&A1,A1:A9,"<="&A7)

 

[Marwan69]

Thanks so much RoryA, perfectly worked however, didn't get the phrase: "The value in column A can't be both A1 and A7, since they are different.".

Isn't that I'm looking up two different dates within column A ("Dates")?

Many thanks.

 

[RoryA]

Your original formula translates to this:

Add up column B where the value in column A is equal to A1 and is also equal to A7.

If A1 and A7 are not the same, the value in column A can never match both of them at the same time.

 

[Marwan69]

Aha...Got it! Thanks.