# 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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)

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

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

The formula is perfect. Thanks for your help.

Replies
6
Views
111
Replies
1
Views
80
Replies
6
Views
78
Replies
3
Views
476
Legacy 143009
L
Replies
12
Views
607

Threads
1,214,437
Messages
6,119,516
Members
448,903
Latest member
StephMJ

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back