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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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,459
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,897
Members
431,772
Latest member
dannyboi1

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

Which adblocker are you using?

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
Top