Microsoft Excel 2007 Formula Question

I have an Excel worksheet (named "Consolidated") where Column A is "Date", Column B is called "Type of Review." Each of these columns have different names and dates associated with them. In column F I have a value. So for example:
Column A        Column B         Column F
4/9/2012            Type A            100
4/9/2012            Type B             50
4/10/2012          Type A            110
4/10/2012          Type B             50

Rather than run a Pivot Table, which I am aware of, I have a new worksheet (named "Aggregate") where I am looking to sum/aggregate all of Column F by what it is labeled in Column B -- so here I would have Type A = 210 (regardless of the date) and Type B = 100. One of my colleagues recommended a sumproduct for getting this result. I am a beginner so detailed help would be useful and appreciated.
mmcgillo88Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
Assuming that worksheet Aggregate cell A2 contains Type A, here are three ways of getting your answer:

Using SUMPRODUCT:
=SUMPRODUCT(--(Consolidated!B$2:B$1000=A2),Consolidated!F$2:F$1000)      'The -- is the unitary operator, and converts TRUE/FALSE into 1 and 0

Using SUMIF:
=SUMIF(Consolidated!B$2:B$1000,A2,Consolidated!F$2:F$1000)

Using SUMIFS:
=SUMIFS(Consolidated!F$2:F$1000,Consolidated!B$2:B$1000,A2)                   'You may have additional test ranges and criteria to the right
0
 
mmcgillo88Author Commented:
Quick response, exactly what I needed. If you could do an overview of sumproduct function that would be very helpful as well.
0
 
byundtCommented:
You may find Microsoft Excel MVP Bob Phillip's web page on SUMPRODUCT to provide a good explanation of how it works: http://www.xldynamic.com/source/xld.SUMPRODUCT.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.