Sumproduct IF in a loop

[LS7]

Hello All, this is my first time using the forum to post a question but your answers have been helping me out in the past

I am having a hard time figuring this one out and putting it all together.

I have data in column A that represents account numbers whose counts will be different every time I run the sheet. There are two other variables associated with the account numbers that I use in a calculation, such as a percentage and a return. The scope is to get an aggregate return. So I would use the simple sUMPRODUCT(H3:H7,J3:J7) for example.

So the data looks somewhat like this:
A H J
XX 2.5 75%
XX 3.7 15%
XX 4.31 10%
YYY 3.2 30%
YYY 1.2 40%
YYY 0.5 15%
YYY 3.5 15%
DD 2.2 80%
DD 1.5 20%

Column A will not be constant, that is XX isn't always going to show up first, and it will not always have only 3 rows of data.

I need a macro or formula that will sumproduct H and J for applicable account numbers. So for XX the solution would be in cell K3, for YYY in cell K6, etc.

I was thinking about inserting empty row via a macro between account numbers and loop the calculation until empty row, but I am having a hard time making it work.
Any suggestions, either macro or some sophisitacated sumproduct formula that I don't know how to write would be very helpful. Even if you point me in the right direction on what to research, I would be greatful!!

Thank you soooo much!

 

[texasalynn]

Welcome to MrExcel board...

you need to include the variable in your formula

Excel Workbook
	K	L
3	XX	2.861
Sheet2

Excel 2003

Cell Formulas
Range	Formula
L3	=SUMPRODUCT(--(A3:A7=K3),(H3:H7*J3:J7))

 

[Aladin Akyurek]

Also...

-SUMPRODUCT(--(A3:A7=K3),H3:H7,J3:J7)

which is slightly faster.

 

[LS7]

THANK YOU SO MUCH!! I figured it all out! you're the best!

for FYI in case someone else needs this:

I needed to know the count of each occurance:
Criteria:
COUNTIF($A$10:A10,A10)=1

Count:
IF(DCOUNTA($A$9:$A$98,1,$L9:$L10)=0,"",(DCOUNTA($A$9:$A$106,1,$L9:$L10)))


I did this because I didn't want to show it multiple time

I needed the percentage
F(A10=A10,K10/VLOOKUP(A10,$B$1:$D$5,3,FALSE),"")

and then I needed the sumproduct if

IF(M10<>"",SUMPRODUCT(--(A$9:A$65536=A10),G$9:G$65536,N$9:N$65536),"")