How to count items depending on attributes in VBA

  • Thread starter Thread starter MIchael
  • Start date Start date
M

MIchael

Hello
I have a table
Each line is about a window and its attributes.
One of the attributes is the floor on which the window is installed and an other is the class (1-5) of the window
What I need to do is: I want to count the windows on each floor from floor 1-x and depending on its class.
So the table looks like that:


Window
Number floor class
1 1 1
2 1 1
3 2 3
4 2 3
5 2 2
.. . .
.. . .
.. . .

So the result should be :
2 Windows on floor 1 with class 1
2 Windows on floor 2 with class 3
1 Window on floor 2 with class 2
and so on...... for the other floors

in VBA

I tried the function dcount but did not succeed because the criteria should not be in the table
and I dont know how to loop it..
Dim winClass1 as long
winClass1 = Application.WorksheetFunction.DCountA(Range("A:F"), 2, Range("H1:J2"))

Thank you very much for any help!

MIchael
 
=sumproduct(--(B2:B20=1),--(C2:C20=1))

gives fllor 1, class 1, adfjust for others.

You could use a table like thins on another sheet

A.......................B.........C.........D
Floor/Class........1..........2..........3
1.........1
2.........2
3.........3

and use

=sumproduct(--(Sheet1!$B$2:$B$20=$A2),--(Sheet1!$C$2:$C$20=B$1))

and copy down and across


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks for your help
but I needed it for vba
but I solved it in the meanwhile
If anyone is interested:

ciao
MIchael

Dim FeSSK2erf As Long
Dim FeSSK3erf As Long
Dim FeSSK4erf As Long
Dim FeSSK5erf As Long
Dim FeSSK2geb As Long
Dim FeSSK3geb As Long
Dim FeSSK4geb As Long
Dim FeSSK5geb As Long
Dim Lufterf As Long
Dim Luftgeb As Long
Dim Stockzähler As Long
Dim Stockzähler1 As String


Stockzähler1 = "KG"
k = 1
For i = 1 To 300 'Anzahl Fenster
Workbooks("PSS-BonnBadGodesberg-Annabe-27-Rübenach.xls").Worksheets("Fensterliste").Activate
FeSSK2erf = Evaluate("sumproduct((d11:d300&z11:z300=""" & Stockzähler1 & "2"")*1)")
FeSSK3erf = Evaluate("sumproduct((d11:d300&z11:z300=""" & Stockzähler1 & "3"")*1)")
FeSSK4erf = Evaluate("sumproduct((d11:d300&z11:z300=""" & Stockzähler1 & "4"")*1)")
FeSSK5erf = Evaluate("sumproduct((d11:d300&z11:z300=""" & Stockzähler1 & "5"")*1)")
FeSSK2geb = Evaluate("sumproduct((d11:d300&z11:z300&AQ11:AQ300=""" & Stockzähler1 & "2" & "ja"")*1)")
FeSSK3geb = Evaluate("sumproduct((d11:d300&z11:z300&AQ11:AQ300=""" & Stockzähler1 & "3" & "ja"")*1)")
FeSSK4geb = Evaluate("sumproduct((d11:d300&z11:z300&AQ11:AQ300=""" & Stockzähler1 & "4" & "ja"")*1)")
FeSSK5geb = Evaluate("sumproduct((d11:d300&z11:z300&AQ11:AQ300=""" & Stockzähler1 & "5" & "ja"")*1)")
Lufterf = Evaluate("sumproduct((d11:d300&ai11:ai300=""" & Stockzähler1 & "erforderlich"")*1)")
Luftgeb = Evaluate("sumproduct((d11:d300&ai11:ai300&AQ11:AQ300=""" & Stockzähler1 & "erforderlich" & "ja"")*1)")

FeSSK5erf = Evaluate("sumproduct((d11:d300&z11:z300=""" & Stockzähler1 & "5"")*1)")




If Stockzähler1 = "KG" Then
Stockzähler1 = "EG"
ElseIf Stockzähler1 = "EG" Then
Stockzähler = 0
Stockzähler1 = Stockzähler + 1 & ".OG"
Stockzähler = Stockzähler + 1
Else
Stockzähler1 = Stockzähler + 1 & ".OG"
Stockzähler = Stockzähler + 1
End If
'j

Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 10).Value = FeSSK2erf
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 11).Value = FeSSK3erf
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 12).Value = FeSSK4erf
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 13).Value = FeSSK5erf
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 15).Value = FeSSK2geb
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 16).Value = FeSSK3geb
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 17).Value = FeSSK4geb
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 18).Value = FeSSK5geb
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 14).Value = Lufterf
Workbooks("Test.xls").Worksheets("Tabelle1").Cells(k, 19).Value = Luftgeb




Next i
 
Back
Top