Exact Precedents

[Abdul Shakeel]

Hi All,

I m using sum if function to sum values with specific criteria...the problem
occurs when I want to know that exact precedents of the formula is there any
way to do so.

 

[Dave]

Example please
Dave.

 

[Billy Liddel]

I think this needs a UDF. In the SUMIF function:
=SUMIF($A$2:$A$8,"Mid",$C$2:$C$8)

The above would work but not if you used SUMPRODUCT

Function GetCriteria(ref) As String
'For SUMIF function
Dim str As String, first As Integer, last As Integer
str = ref.Formula
first = Application.Find(",", str) + 2
last = Application.Find(",", str, first) - 1
GetCriteria = Mid(str, first, last - first)
End Function

Fuction copied to a VB Module and entered as =getcriteria(B2) where B2 is
the cell containing the formula.

Regards
Peter

Regards
Peter

 

[Abdul Shakeel]

Let I v a range A1 to B5 In which I have Data just like this
A 100
B 110
A 115
A 120
B 125

in cell C1 I typed this formula Sumif(A1:A5,"=B",B1:B5) & result will be 235

I just want that when I edit the formula reseltunt cells selects
automatically thats in this example are A2 & A5.

 

[Billy Liddel]

Abdul

Normally, we test the formula on a range werre we know the result then trust
Excel to handle the rest. I pasted you data into cells A19:b23 and the
formula I gave returned =B, which was correct.

To find the number of Bs in the range you could use the COUNTIF function
COUNTIF(Range,Criteria) or sumproduct:

=SUMPRODUCT(--(A19:A23="B"))

Both return 2 in your example.

Using SUMPRODUCT as an option to SUMIF you would use

=SUMPRODUCT(--($A$19:$A$23=A20)*B19:B23)

or do you want a function that, using my range, declares something like
"Rows 20, 23" ?

Perhaps you might find the Formula Auditing in the Tools Menu to trace
precedents.

Regards
Peter

 

[Abdul Shakeel]

Dear Billy

First of all thanks for your answer....but I just want that when I press
Ctrl + [ in my sumif holding formula it select only those cell that meets my
given criteria specification, rather select the range A1:B23.

 

[Billy Liddel]

Abdul

Sorry for the delay. You need a macro to select the cells. Copy this to a VB
module shown in a previous post, and return to the sheet.
Press ALT + F8 and click once on the name if it is not selected
Click Options
Assign a letter to the macro. (note that if you select Ctrl + C as the
shortcut, you will not be able to use that shortcut to copy a selection in
this workbook)

Sub SelectPrecedents()
Dim rng As String, rng2 As String, frml As String
Dim frst As Integer, scnd As Integer, thrd As Integer, last As Integer
Dim critA As Integer, critB As Integer, first As Integer
Dim Crit As String, colA As Integer, colB As Integer, offsetCol As Integer
Dim c, d, SelRange As Range, row As Long

' continue if the formula in not Sumif type
On Error Resume Next
For Each c In Selection
SelRange = ""
c.Select
If c.HasFormula = False Then
MsgBox "You must select cells with formulas!"
Exit Sub
End If
'gather info from formula
frml = ActiveCell.Formula
frst = Application.Find("(", frml) + 1
scnd = Application.Find(",", frml)
thrd = Application.Find(",", frml, scnd) + 6
last = Len(frml)
first = Application.Find(",", frml) + 3
critA = Application.Find(",", frml) + 3
critB = Application.Find(",", frml, critA) - 1
Crit = Mid(frml, critA, critB - critA)
rng = Mid(frml, frst, scnd - frst)
rng2 = Mid(frml, thrd, last - thrd)
colA = Range(rng).Column
colB = Range(rng2).Column
For Each d In Range(rng)
'.Activate
row = d.row
If d.Value = Crit Then
If SelRange Is Nothing Then
Set SelRange = Range(Cells(row, colA), Cells(row, colB))
Else
Set SelRange = Application.Union(SelRange, Range(Cells(row, colA),
Cells(row, colB)))
End If
End If
Next d
SelRange.Select
Next c
End Sub

Note that it is only suitable to inspect a SUMIF function and use only on a
single formula at a time.

Regards
Peter Atherton

Dear Billy

First of all thanks for your answer....but I just want that when I press
Ctrl + [ in my sumif holding formula it select only those cell that meets my
given criteria specification, rather select the range A1:B23.

Abdul

Normally, we test the formula on a range werre we know the result then trust
Excel to handle the rest. I pasted you data into cells A19:b23 and the
formula I gave returned =B, which was correct.

To find the number of Bs in the range you could use the COUNTIF function
COUNTIF(Range,Criteria) or sumproduct:

=SUMPRODUCT(--(A19:A23="B"))

Both return 2 in your example.

Using SUMPRODUCT as an option to SUMIF you would use

=SUMPRODUCT(--($A$19:$A$23=A20)*B19:B23)

or do you want a function that, using my range, declares something like
"Rows 20, 23" ?

Perhaps you might find the Formula Auditing in the Tools Menu to trace
precedents.

Regards
Peter

 

[Abdul Shakeel]

Dear Billy

Your code works great...thank you very much for your time &
consideration...but even now one more problem occur with this code when I use
it as
=SUMIF(A1:A5,"=Apples",B1:B5) it works great but when I use it as
=SUMIF(A1:A5,A6,B1:B5) nothing happens, here A6 is the cell which contains
my criteria value APPLE, please do favor me in this regard.

Abdul

Sorry for the delay. You need a macro to select the cells. Copy this to a VB
module shown in a previous post, and return to the sheet.
Press ALT + F8 and click once on the name if it is not selected
Click Options
Assign a letter to the macro. (note that if you select Ctrl + C as the
shortcut, you will not be able to use that shortcut to copy a selection in
this workbook)

Sub SelectPrecedents()
Dim rng As String, rng2 As String, frml As String
Dim frst As Integer, scnd As Integer, thrd As Integer, last As Integer
Dim critA As Integer, critB As Integer, first As Integer
Dim Crit As String, colA As Integer, colB As Integer, offsetCol As Integer
Dim c, d, SelRange As Range, row As Long

' continue if the formula in not Sumif type
On Error Resume Next
For Each c In Selection
SelRange = ""
c.Select
If c.HasFormula = False Then
MsgBox "You must select cells with formulas!"
Exit Sub
End If
'gather info from formula
frml = ActiveCell.Formula
frst = Application.Find("(", frml) + 1
scnd = Application.Find(",", frml)
thrd = Application.Find(",", frml, scnd) + 6
last = Len(frml)
first = Application.Find(",", frml) + 3
critA = Application.Find(",", frml) + 3
critB = Application.Find(",", frml, critA) - 1
Crit = Mid(frml, critA, critB - critA)
rng = Mid(frml, frst, scnd - frst)
rng2 = Mid(frml, thrd, last - thrd)
colA = Range(rng).Column
colB = Range(rng2).Column
For Each d In Range(rng)
'.Activate
row = d.row
If d.Value = Crit Then
If SelRange Is Nothing Then
Set SelRange = Range(Cells(row, colA), Cells(row, colB))
Else
Set SelRange = Application.Union(SelRange, Range(Cells(row, colA),
Cells(row, colB)))
End If
End If
Next d
SelRange.Select
Next c
End Sub

Note that it is only suitable to inspect a SUMIF function and use only on a
single formula at a time.

Regards
Peter Atherton

Dear Billy

First of all thanks for your answer....but I just want that when I press
Ctrl + [ in my sumif holding formula it select only those cell that meets my
given criteria specification, rather select the range A1:B23.

Abdul

Normally, we test the formula on a range werre we know the result then trust
Excel to handle the rest. I pasted you data into cells A19:b23 and the
formula I gave returned =B, which was correct.

To find the number of Bs in the range you could use the COUNTIF function
COUNTIF(Range,Criteria) or sumproduct:

=SUMPRODUCT(--(A19:A23="B"))

Both return 2 in your example.

Using SUMPRODUCT as an option to SUMIF you would use

=SUMPRODUCT(--($A$19:$A$23=A20)*B19:B23)

or do you want a function that, using my range, declares something like
"Rows 20, 23" ?

Perhaps you might find the Formula Auditing in the Tools Menu to trace
precedents.

Regards
Peter


:

Let I v a range A1 to B5 In which I have Data just like this
A 100
B 110
A 115
A 120
B 125

in cell C1 I typed this formula Sumif(A1:A5,"=B",B1:B5) & result will be 235

I just want that when I edit the formula reseltunt cells selects
automatically thats in this example are A2 & A5.


:

Example please
Dave.

 

[Shakeel]

Dear Billy

Your code works great...thank you very much for your time &
consideration...but even now one more problem occur with this code when I use
it as
=SUMIF(A1:A5,"=Apples",B1:B5) it works great but when I use it as
=SUMIF(A1:A5,A6,B1:B5) nothing happens, here A6 is the cell which contains
my criteria value APPLE, please do favor me in this regard.

Abdul

Sorry for the delay. You need a macro to select the cells. Copy this to a VB
module shown in a previous post, and return to the sheet.
Press ALT + F8 and click once on the name if it is not selected
Click Options
Assign a letter to the macro. (note that if you select Ctrl + C as the
shortcut, you will not be able to use that shortcut to copy a selection in
this workbook)

Sub SelectPrecedents()
Dim rng As String, rng2 As String, frml As String
Dim frst As Integer, scnd As Integer, thrd As Integer, last As Integer
Dim critA As Integer, critB As Integer, first As Integer
Dim Crit As String, colA As Integer, colB As Integer, offsetCol As Integer
Dim c, d, SelRange As Range, row As Long

' continue if the formula in not Sumif type
On Error Resume Next
For Each c In Selection
SelRange = ""
c.Select
If c.HasFormula = False Then
MsgBox "You must select cells with formulas!"
Exit Sub
End If
'gather info from formula
frml = ActiveCell.Formula
frst = Application.Find("(", frml) + 1
scnd = Application.Find(",", frml)
thrd = Application.Find(",", frml, scnd) + 6
last = Len(frml)
first = Application.Find(",", frml) + 3
critA = Application.Find(",", frml) + 3
critB = Application.Find(",", frml, critA) - 1
Crit = Mid(frml, critA, critB - critA)
rng = Mid(frml, frst, scnd - frst)
rng2 = Mid(frml, thrd, last - thrd)
colA = Range(rng).Column
colB = Range(rng2).Column
For Each d In Range(rng)
'.Activate
row = d.row
If d.Value = Crit Then
If SelRange Is Nothing Then
Set SelRange = Range(Cells(row, colA), Cells(row, colB))
Else
Set SelRange = Application.Union(SelRange, Range(Cells(row, colA),
Cells(row, colB)))
End If
End If
Next d
SelRange.Select
Next c
End Sub

Note that it is only suitable to inspect a SUMIF function and use only on a
single formula at a time.

Regards
Peter Atherton

Dear Billy

First of all thanks for your answer....but I just want that when I press
Ctrl + [ in my sumif holding formula it select only those cell that meets my
given criteria specification, rather select the range A1:B23.

Abdul

Normally, we test the formula on a range werre we know the result then trust
Excel to handle the rest. I pasted you data into cells A19:b23 and the
formula I gave returned =B, which was correct.

To find the number of Bs in the range you could use the COUNTIF function
COUNTIF(Range,Criteria) or sumproduct:

=SUMPRODUCT(--(A19:A23="B"))

Both return 2 in your example.

Using SUMPRODUCT as an option to SUMIF you would use

=SUMPRODUCT(--($A$19:$A$23=A20)*B19:B23)

or do you want a function that, using my range, declares something like
"Rows 20, 23" ?

Perhaps you might find the Formula Auditing in the Tools Menu to trace
precedents.

Regards
Peter


:

Let I v a range A1 to B5 In which I have Data just like this
A 100
B 110
A 115
A 120
B 125

in cell C1 I typed this formula Sumif(A1:A5,"=B",B1:B5) & result will be 235

I just want that when I edit the formula reseltunt cells selects
automatically thats in this example are A2 & A5.


:

Example please
Dave.