SUMing two IF Values

**HiDbLevel** · 01-02-2009, 05:23 PM

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** · 01-02-2009, 05:31 PM

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** · 01-02-2009, 05:39 PM

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** · 01-02-2009, 05:53 PM

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** · 01-02-2009, 05:56 PM

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

Thread: SUMing two IF Values

Thread Tools

Display

SUMing two IF Values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions