Prev: LOOKUP VALUE OCCURS >ONCE IN TABLE ARRAY - RETURN LAST OCCURRE
Next: Duplicate value address search - apologies for cross post
From: Seanie on 13 Feb 2010 09:57
I wish to sum all data in Sheet2 Column J, if the value in Sheet2
Column B = The value in Sheet1 Column A

I've tried =SUMPRODUCT(PC_Month_Locations=A8,PC_Month)

Named Ranges above used instead of absolute references
From: Dave Peterson on 13 Feb 2010 10:10
If your ranges are defined correctly:

=sumif(pc_month_locations,a8,pc_month)

If you wanted to use =sumproduct():
=SUMPRODUCT(--(PC_Month_Locations=A8),PC_Month)

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Seanie wrote:
>
> I wish to sum all data in Sheet2 Column J, if the value in Sheet2
> Column B = The value in Sheet1 Column A
>
> I've tried =SUMPRODUCT(PC_Month_Locations=A8,PC_Month)
>
> Named Ranges above used instead of absolute references

--

Dave Peterson
From: Seanie on 13 Feb 2010 10:19
Bingo, works great, Thanks


From: Teethless mama on 13 Feb 2010 10:44
=SUMIF(PC_Month_Locations,A8,PC_Month)


"Seanie" wrote:

> I wish to sum all data in Sheet2 Column J, if the value in Sheet2
> Column B = The value in Sheet1 Column A
>
> I've tried =SUMPRODUCT(PC_Month_Locations=A8,PC_Month)
>
> Named Ranges above used instead of absolute references
> .
>
 | 
Pages: 1
Prev: LOOKUP VALUE OCCURS >ONCE IN TABLE ARRAY - RETURN LAST OCCURRE
Next: Duplicate value address search - apologies for cross post