Count If

[aks.1982]

Hi All,

Please find the attached file.

what i need is that,the month column (F) should extract only months, (June for 10-06-12) not the date
from the date column(A) and then it should each commodity from "B" column to "G" Column
individually then in the "H" column it should sum the whole expense for the month according to the commodities
and date as well and if the expenses are above the budgeted amount which is mentioned at the top of the sheet
should give an indication as well,like a color change or similar and the difference amount should be mentioned at the next column "I"
,if its below budget same as like above mentioned..

Hope you understand guys..and Thanks in advance..

Thanks,
Ak

[CatDaddy]

[vba]Sub alex()
Dim cell As Range, cell2 As Range
Dim lr As Integer, lr2 As Integer, r As Integer
Dim price As Double
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
price = 0
For Each cell In Range("B5:B" & lr)
cell.Value = Format(cell.Offset(0, -1).Text, "mmm")
Next cell
Range("B5:C" & lr).Copy
Range("G8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$G$8:$H$" & (lr + 3)).RemoveDuplicates Columns:=Array(1, 2), Header _
:=xlYes
lr2 = Range("G" & Rows.Count).End(xlUp).Row
For Each cell In Range("G8:G" & lr2)
r = cell.Row

For Each cell2 In Range("B5:B" & lr)

If cell.Text = cell2.Text And cell.Offset(0, 1).Text = cell2.Offset(0, 1).Text Then
price = price + cell2.Offset(0, 2).Value
End If
Next cell2

Range("I" & r).Value = price
price = 0

Select Case (cell.Offset(0, 1).Text)

Case "Apple"
If Range("I" & r).Value > Range("I3").Value Then
Range("J" & r).Value = Range("I3").Value - Range("I" & r).Value
Else
Range("K" & r).Value = Range("I" & r).Value - Range("I3").Value
End If

Case "Orange"
If Range("I" & r).Value > Range("I4").Value Then
Range("J" & r).Value = Range("I4").Value - Range("I" & r).Value
Else
Range("K" & r).Value = Range("I" & r).Value - Range("I4").Value
End If
End Select
Next cell
End Sub[/vba]

I made a little change to your source data

[aks.1982]

Thanks Friend it works perfectly fine..i had a small doubt too
if i had similar type of commodity on the commodity column can we sum it
together..

Eg: I Had 2 Similar Commodity Such as Apple and Apple.Gala and in the expense
column it should count the sum of both..

How can we edit the macros according to that..

Thanks,
Ak

[CatDaddy]

[VBA]Sub alex()
Dim cell As Range, cell2 As Range
Dim lr As Integer, lr2 As Integer, r As Integer
Dim price As Double
ActiveWorkbook.Sheets(1).Activate
lr = Range("A" & Rows.Count).End(xlUp).Row
price = 0
For Each cell In Range("B5:B" & lr)
cell.Value = Format(cell.Offset(0, -1).Text, "mmm")
Next cell
Range("B5:C" & lr).Copy
Range("G8").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$G$8:$H$" & (lr + 3)).RemoveDuplicates Columns:=Array(1, 2), Header _
:=xlYes
lr2 = Range("G" & Rows.Count).End(xlUp).Row
For Each cell In Range("G8:G" & lr2)
r = cell.Row

For Each cell2 In Range("B5:B" & lr)

If cell.Text = cell2.Text And (cell.Offset(0, 1).Text = cell2.Offset(0, 1).Text _
Or InStr(cell2.Offset(0, 1).Text, cell.Offset(0, 1).Text) > 0) Then
price = price + cell2.Offset(0, 2).Value
End If
Next cell2

Range("I" & r).Value = price
price = 0

Select Case (cell.Offset(0, 1).Text)

Case "Apple"
If Range("I" & r).Value > Range("I3").Value Then
Range("J" & r).Value = Range("I3").Value - Range("I" & r).Value
Else
Range("K" & r).Value = Range("I" & r).Value - Range("I3").Value
End If

Case "Orange"
If Range("I" & r).Value > Range("I4").Value Then
Range("J" & r).Value = Range("I4").Value - Range("I" & r).Value
Else
Range("K" & r).Value = Range("I" & r).Value - Range("I4").Value
End If
End Select
Next cell
End Sub[/VBA]

[aks.1982]

Thanks friend for your response..please find my attached file,it seems to be ok..the values of both commodities is adding together and it is displaying separtly too,what i need is adding together no need of displaying and the other problem is whenever i update the file it is taking bottom of my table tables..please have a look on the attached file..

Thanks,
Ak

[aks.1982]

Somebody please have a look on the matter and give me a solution please..

Thanks,