summing up values with two variables

K

Kmitchell

Active Member
Joined
Feb 27, 2007
Messages
365
Office Version
  1. 365
Platform
  1. Windows
Hello,

Is there a way to say if = January and apple ($b$62 & $c$61), then take the value equal to that criteria from $b$4:$c$57, and do the following:

take that value that meets that (D6) criteria then have a formula that says:

value found using above if statement multiplied always by e53 then divided by c53.

Hopefully this makes sense.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
I am not following very well what you are looking to do but I assume that it can be done somehow. I would need some clarification though... others may get what you want though. Perhaps some sample data would be beneficial though.
 
Upvote 0
I'm not sure how to attach a file or image, let me give this another shot.

If = apple and october (B62 & C61) then find apple and october from B4:C47, and take data from D4:D47.

Maybe this is a Vlookup?
 
Upvote 0
I'm not sure how to attach a file or image, let me give this another shot.

If = apple and october (B62 & C61) then find apple and october from B4:C47, and take data from D4:D47.

Maybe this is a Vlookup?
 
Upvote 0
Are there multiple Apple entries and October entries in your Table and you only wish to return the value in D where B and C match B62 and C61?
 
Upvote 0
Assuming that is the case you might try a UDF:

Right click the sheet tab and select view code, right click one of the sheets in the workbook and insert a module.

Paste this in:

Code: 
Option Compare Text
Function Lookup2(Ce1 As Range, Ce2 As Range, Range1 As Range)
Dim ce As Range

Application.Volatile
For Each ce In Range1
    If ce = Ce1 And ce.Offset(0, 1) = Ce2 Then Lookup2 = ce.Offset(0, 2)
Next ce

End Function

To use this:
=Lookup2(FirstCell, SecondCell, Range)

FirstCell needs to be what you want found in the first column of your data table. SecondCell is the cell that you want found in the second column of data and Range should be your first column of your data range. So as near as I can tell from your explanation it might look something like this:

=Lookup2(B62, C61, B4:B47)

I am sure some of the formula gurus have some way to work this out wich may fit your needs better.

I hope this helps!

Edit ~ I just realized that you're titled mentioned something about summing... if you are looking to sum those up you could do this

Code: 
Function SumLookup2(Ce1 As Range, Ce2 As Range, Range1 As Range)
Dim ce As Range

Application.Volatile
For Each ce In Range1
    If ce = Ce1 And ce.Offset(0, 1) = Ce2 Then SumLookup2 = ce.Offset(0, 2) + SumLookup2
Next ce

End Function

This would be used in the same way

=SumLookup2(FirstCell, SecondCell, Range)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Calculate sums based on values
Replies
3
Views
338
NateSC
NateSC
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
325
chazrab
C
C
  • Solved
How to pick up the last value in a column to use in a formula
Replies
2
Views
282
chariot101
C
D
  • Question Question
Formula to parse string then sum values
Replies
8
Views
827
Bosquedeguate
Bosquedeguate
T
  • Solved
Two Value if true, one value if false
Replies
5
Views
497
Joe4
Joe4

Forum statistics

Threads
1,225,489
Messages
6,185,284
Members
453,285
Latest member
Wullay

Share this page

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
Back
Top