Formula Help

[ozstephan]

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.

 

[Brian from Maui]

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]

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]

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]

The formula is perfect. Thanks for your help.