Formula Help

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
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.
 

Some videos you may like

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
Joined
Feb 16, 2002
Messages
8,458
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
Joined
Nov 19, 2005
Messages
52
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
Joined
Feb 16, 2002
Messages
8,458
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,377
Members
412,589
Latest member
ArtBOM
Top