Sum formula across multiple tabs and fields

R

rylv5050

I have a workbook with multiple tabs and a summary screen that totals counts
of cell phones by site. Each tab is a site with users and their cell phone
data, including cellular type, vendor etc. The summary screen looks like
this but there are 7 total types of cell phone for each of 4 vendors:
A B C D
E
1 Location A Vendor A Cell Phone Count Black Berry Count
Palm
2 Vendor B
3 Vendor C
4 Vendor D

Each Tab is a location "Location A", Location B etc. and I want to insert
formulas in C1-C4, D1-D4 on this summary page that will look to every vendor
tab and pull the count for Vendor which is in colum A2-A1000 and Cell Phone
type in G2-G1000. This formula should give me an accurate count of devices
by vendor which will change autmatically when the tabs of locations changes
dynamically.

I appreciate it!!!!
 
N

NBVC

rylv5050;447485 said:
I have a workbook with multiple tabs and a summary screen that total
counts
of cell phones by site. Each tab is a site with users and their cel
phone
data, including cellular type, vendor etc. The summary screen look
like
this but there are 7 total types of cell phone for each of 4 vendors:
A B C
D
E
1 Location A Vendor A Cell Phone Count Black Berr
Count
Palm
2 Vendor B
3 Vendor C
4 Vendor D

Each Tab is a location "Location A", Location B etc. and I want t
insert
formulas in C1-C4, D1-D4 on this summary page that will look to ever
vendor
tab and pull the count for Vendor which is in colum A2-A1000 and Cel
Phone
type in G2-G1000. This formula should give me an accurate count o
devices
by vendor which will change autmatically when the tabs of location
changes
dynamically.

I appreciate it!!!!

IF you download and install a free addin called Morefunc.xll from here


'Morefunc - Free software downloads and software reviews - CNE
Download.com
(http://download.cnet.com/Morefunc/3000-2077_4-10423159.html)



you can then use the Countif.3D() function

e.g

=COUNTIF.3D(Sheet1:Sheet5!$A$1:$A$200,A1)

where Sheet1 is the first sheet and Sheet5 is the last sheet an
A1:A200 contain the vendor names in each sheet..... and A1 on activ
sheet contains "Vendor 1" name to look for.

formula can be copied down

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com
 
R

rylv5050

This didn't correct my formula but perhaps if I clarify my need, we can get
there.

On my summary sheet, I need to create a countif a2:a300 = X and G2:G300 = Y
then I want the # returned in each respective field. So count how many
Verizon/Cell Phones there are, Verizon/Black Berries there are,
ALLTEL/CellPhones etc. I should also point out that I need to count them
based on their alpha data but I just keep returning 0 with the ollowing
attempt:
=COUNTIFS('Battle Mountain'!A2:A300,ALLTEL,'Battle Mountain'!G2:G300,CELL
PHONE )
 
N

NBVC

For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachments to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so. :)


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top