SUMing two IF Values

[HiDbLevel]

Here's what I want the formula to do for me:
I'm in regional sales and need to track sales figures (column C) within a City (column A) within a specific date range (column B). See attached xls example file.
I have tried nesting AND and SUM into an IF formula as well as an INDEX formula with no success.
Any thoughts? Thanks!

[VBA Noob]

Maybe

=SUMPRODUCT((Sales!$A$2:$A$12='City Raw Data'!$A2)*(YEAR(Sales!$B$2:$B$12)=RIGHT(B$1,4)+0)*(Sales!$C$2:$C$12))

VBA Noob

[pinmaster]

Hi,

Try something like this in B2 of the "city raw data" sheet then drag across and down:

=SUMPRODUCT((Sales!$A$2:$A$12=$A2)*(YEAR(Sales!$B$2:$B$12)=ABS(RIGHT(B$1,4))),Sales!$C$2:$C$12)

HTH
Jean-Guy

[HiDbLevel]

SUMPRODUCT worked like a champ. Thanks! Although, I'm curious because I have no clue how it knows the year I'm looking for when the cell (B1) said "Sales 2008". I didn't know that excel could look for numbers within a text/num cell. Interesting.

[VBA Noob]

The right function returns the last 4 characters e.g returns 2007 for text "Sales 2007" and by adding the zero it converts it to a number

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

VBA Noon