# Formula Help

#### ozstephan

##### Board Regular
I am trying to work a formula and can't find the solution. Need to compare two columns based on rows which I have done with

=sumproduct((C1:C300=A170)*(D1:D300=B171))

So I've compared the two colums(C&D) to find the rows I need. The third part I cannot solve is I then need to add up all values in column J that are identified by the first part of the formula.

If anyone can help it would be appreciated.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Brian from Maui

##### MrExcel MVP
ozstephan said:
I am trying to work a formula and can't find the solution. Need to compare two columns based on rows which I have done with

=sumproduct((C1:C300=A170)*(D1:D300=B171))

So I've compared the two colums(C&D) to find the rows I need. The third part I cannot solve is I then need to add up all values in column J that are identified by the first part of the formula.

If anyone can help it would be appreciated.

Try,

=SUMPRODUCT(--(C1:C300=A179),--(D1:D300=B171),J1:J300)

#### ozstephan

##### Board Regular
Try,

=SUMPRODUCT(--(C1:C300=A179),--(D1:D300=B171),J1:J300)

Thanks Brian, this worked perfectly. I have another version of this query I am trying to solve.

If I want to use the same columns and cells but want it only to count the number of cells in J that e.g are greater that 5. eg2. are between 2 & 4.
This problem is using the same criteria to narrow cells C&D but tells me how many cells in J that meet the criteria are over or under a figure.

If anyone can help?
Thanks

#### Brian from Maui

##### MrExcel MVP
ozstephan said:
Try,

=SUMPRODUCT(--(C1:C300=A179),--(D1:D300=B171),J1:J300)

Thanks Brian, this worked perfectly. I have another version of this query I am trying to solve.

If I want to use the same columns and cells but want it only to count the number of cells in J that e.g are greater that 5. eg2. are between 2 & 4.
This problem is using the same criteria to narrow cells C&D but tells me how many cells in J that meet the criteria are over or under a figure.

If anyone can help?
Thanks

If I understand correctly,

=SUMPRODUCT(--(C1:C300=A179),--(D1:D300=B171),--(J1:J300 > 5))

=SUMPRODUCT(--(C1:C300=A179),--(D1:D300=B171),--(J1:J300 > 2),--(J1:J300 < 4))

Assuming for the last formula you have decimal numbers, if not use =3

#### ozstephan

##### Board Regular
The formula is perfect. Thanks for your help.

Replies
2
Views
57
Replies
1
Views
46
Replies
3
Views
270
Replies
3
Views
29
Replies
3
Views
28