Funtion Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

This is what I am working with

D
lqrcity inservic
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Quincy
Mendon
Barry
Pittsfield

What I an trying to do is total the number of cells that are for each city for each in service. For instance, how many Quincy are N. I am taking the total and putting the information in a new sheet in the same workbook. I want to use columns instead of cell ranges so the data automatically will update when new records are entered. Does anyone have any recommendations on how to write the formula that I am wanting. Any and all suggestions will be appreciated

Thanks
jess
 
Hi
try
=SUMPRODUCT(($A$1:$A$1000="Quincy")*($B$1:$B$1000="N"))
Note: SUMPRODUCT does not accept ranges like $A$A
 
Jess,

You can't do what you want with columns, it has to be ranges

=SUMPRODUCT((A1:A1000="Quincy")*(B1:B1000="N"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

jess said:
Hi,

This is what I am working with:

D M
lqrcity inservice
Quincy N
Quincy N
Quincy N
Quincy N
Quincy N
Quincy N
Quincy D
Quincy N
Quincy N
Quincy D
Quincy N
Quincy D
Mendon N
Barry N
Pittsfield N

What I an trying to do is total the number of cells that are for each city
Click to expand...
for each in service. For instance, how many Quincy are N. I am taking the
total and putting the information in a new sheet in the same workbook. I
want to use columns instead of cell ranges so the data automatically will
update when new records are entered. Does anyone have any recommendations on
how to write the formula that I am wanting. Any and all suggestions will be
appreciated.
 
Just as another suggestion, I would use pivot Table with a Dynamic range for
this. The dynamic range would self adjust to accomodate new records, and the
Pivot table will just make you go WOW!!

In case it is of interest, a 60 second intro to the Pivot:-

Assuming your data starts in row 1 and finishes row 100, select D1:M100 (From
your example your data would appear to be in these columns) and then do :-

Data / PivotTable and PivotChart report
Hit Next / Next / Finish

Drag IQRCITY to the left of the table and drag INSERVICE to the middle

Done.

better intro here:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Instructions for a dynamic range

http://www.contextures.com/xlPivot01.html#Dynamic

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



jess said:
Hi,

This is what I am working with:

D M
lqrcity inservice
Quincy N
Quincy N
Quincy N
Quincy N
Quincy N
Quincy N
Quincy D
Quincy N
Quincy N
Quincy D
Quincy N
Quincy D
Mendon N
Barry N
Pittsfield N

What I an trying to do is total the number of cells that are for each city for
Click to expand...
each in service. For instance, how many Quincy are N. I am taking the total and
putting the information in a new sheet in the same workbook. I want to use
columns instead of cell ranges so the data automatically will update when new
records are entered. Does anyone have any recommendations on how to write the
formula that I am wanting. Any and all suggestions will be appreciated.
 
thank you

how would you use this refering to a different worksheet in the same workbook?
 
Hi
just at the worksheet reference like
=SUMPRODUCT(('sheet1'!$A$1:$A$1000="Quincy")*('sheet1'!$B$1:$B$1000="N"
))
 
Back
Top