programming
 

look to see the type of device that was tested i.e "heat" and then count all
those and give me a number on the first sheet for that month.
Is the above a static list and the value is inserted beside the required
name or is the list dynamic and created as a unique values from the list that
is being counted?

--
Regards,

OssieMac


"steve s" wrote:

I'm trying to program excel to count up the number of devices on a sheet that
were tested during the month. I have a sheet that has devices in "a" column
i.e. heat pull water. I have another column that I populate after a device
was tested i.e column "I". A different sheet has cells for totals of said
devices that were tested that month. So, the program should look in column
"I" for an "X" showing tested, then look to see the type of device that was
tested i.e "heat" and then count all those and give me a number on the first
sheet for that month. There will be other devices of the same type that will
not have an "x" in the "I" column until next month, so I can't just add up
whatever is on the sheet.

Thanx

Steve S.

programming
 

I'll try to make an example.

A B C D E F G H I
HEAT X
PULL
WATER X

If I test these devices I put an X in column "I". Because there's an "X" in
that column, it looks at column A in that row and counts the device whatever
it is, then adds them up with others that are the same in the column that
might also have an X. In this case it would "see" an "X" in "I" and look at
"A" and "see" "heat". It would add up all "heat" in A that had an X in its
rox in column I, and compile a total for the sheet(s) and put this total on a
different sheet.
Column "A" changes all the time from bldg. to bldg., and so does column "I"
depending on what devices we test.

I hope this is not confusing. Thanx.

Steve S.


"OssieMac" wrote:

look to see the type of device that was tested i.e "heat" and then count all
those and give me a number on the first sheet for that month.
Is the above a static list and the value is inserted beside the required
name or is the list dynamic and created as a unique values from the list that
is being counted?

--
Regards,

OssieMac


"steve s" wrote:

I'm trying to program excel to count up the number of devices on a sheet that
were tested during the month. I have a sheet that has devices in "a" column
i.e. heat pull water. I have another column that I populate after a device
was tested i.e column "I". A different sheet has cells for totals of said
devices that were tested that month. So, the program should look in column
"I" for an "X" showing tested, then look to see the type of device that was
tested i.e "heat" and then count all those and give me a number on the first
sheet for that month. There will be other devices of the same type that will
not have an "x" in the "I" column until next month, so I can't just add up
whatever is on the sheet.

Thanx

Steve S.

programming
 

Hi Steve,

This function should do it. I have assumed is that you have Row one of
the data sheet containing column headings (the code can be modified if
you don't) and also that when you run the macro, the data sheet is the
active sheet. You can hard code sheet names though if you want.

Sub SummaryRep()
Dim SumSheet As Worksheet, SrcSheet As Worksheet
Dim SumRng As Range

Set SrcSheet = ActiveSheet
Range(Range("$A$1"), Range("$A$65536") _
.End(xlUp)).Name = "SumIRange"
Range("SumIRange").Offset(0, 8) _
.Name = "SumCRange"

Set SumSheet = ThisWorkbook.Worksheets.Add

SrcSheet.Range("A:A").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:= _
Range("$A$1"), Unique:=True
Set SumRng = SumSheet.Range("$A$1").CurrentRegion
Set SumRng = SumRng.Offset(1, 1) _
.Resize(SumRng.Rows.Count - 1, 1)

SumSheet.Range("$B$1") = "Number"
SumRng.FormulaR1C1 = _
"=SUMPRODUCT((SumIRange=RC[-1])*(TRIM(SumCRange)=""X""))"
End Sub

Also note that this function will create a new sheet every time it is
run. I used a SUMPRODUCT formula to get the counts, as that way the
numbers will stay somewhat dynamic and it is faster than looping
through the rows to count them manually.

Cheers,
Ivan.

On Apr 1, 12:05*pm, steve s wrote:
I'll try to make an example.

A * * * * * * * *B * * *C * * *D * * *E * * *F * * G * * *H * * I
HEAT * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * X
PULL * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
WATER * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *X

If I test these devices I put an X in column "I". Because there's an "X" in
that column, it looks at column A in that row and counts the device whatever
it is, then adds them up with others that are the same in the column that
might also have an X. In this case it would "see" an "X" in "I" and look at
"A" and "see" "heat". It would add up all "heat" in A that had an X in its
rox in column I, and compile a total for the sheet(s) and put this total on a
different sheet.
Column "A" changes all the time from bldg. to bldg., and so does column "I"
depending on what devices we test.

I hope this is not confusing. Thanx.

Steve S.



"OssieMac" wrote:
look to see the type of device that was tested i.e "heat" and then count all
those and give me a number on the first sheet for that month.
Is the above a static list and the value is inserted beside the required
name or is the list dynamic and created as a unique values from the list that
is being counted?

--
Regards,

OssieMac

"steve s" wrote:

I'm trying to program excel to count up the number of devices on a sheet that
were tested during the month. I have a sheet that has devices in "a" column
i.e. heat pull water. I have another column that I populate after a device
was tested i.e column "I". A different sheet has cells for totals of said
devices that were tested that month. So, the program should look in column
"I" for an "X" showing tested, then look to see the type of device that was
tested i.e "heat" and then count all those and give me a number on the first
sheet for that month. There will be other devices of the same type that will
not have an "x" in the "I" column until next month, so I can't just add up
whatever is on the sheet.

Thanx

Steve S.- Hide quoted text -

- Show quoted text -