In the spreadsheet I set up I have several columns/rows but only 3 of them
are giving me trouble. The 3 columns are PRODUCT , P/L (pump or load) , and
TOTALS that are giving me my trouble. We have several different products and
most are both loaded or pumped at different times during the month. I have to
keep a running total for daily, weekly, monthly and yearly for all product
handling at our facility.

How can I get my spreadsheet to count the number of individual
loading/pumping of each product. At the end of the sheet I SUM the column to
find the totals for all handlings in a month. Right now after I enter the
info in the row for a product I have to go up the sheet and count the # of
times the product had been pumped so far in the month and enter it in the end
row, this done for each product and loading or pumping. We have 20 plus
different products and all are either loaded or pumped during a give month so
trying to track all of the different product handlings is a chore.

EX. we will have 20 gas loading, 25 gas pumping, 12 diesel pumping, 8 diesel
loading, 45 kero loading, 14 vgo pumping and 23 vgo loading for a total of
147. All scattered thur out the month that I have to track daily.

We used to do this with a tablet and pen but I have gotten Excel to do alot
of it just not this part.

tankman,

If I follow you correctly it sounds to me as if you want SUMPRODUCT() read
up about it at:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

(one of many sites I just happend to Google to that one first)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"tankerman" wrote in message
...
In the spreadsheet I set up I have several columns/rows but only 3 of them
are giving me trouble. The 3 columns are PRODUCT , P/L (pump or load) ,
and
TOTALS that are giving me my trouble. We have several different products
and
most are both loaded or pumped at different times during the month. I have
to
keep a running total for daily, weekly, monthly and yearly for all product
handling at our facility.

How can I get my spreadsheet to count the number of individual
loading/pumping of each product. At the end of the sheet I SUM the column
to
find the totals for all handlings in a month. Right now after I enter the
info in the row for a product I have to go up the sheet and count the # of
times the product had been pumped so far in the month and enter it in the
end
row, this done for each product and loading or pumping. We have 20 plus
different products and all are either loaded or pumped during a give month
so
trying to track all of the different product handlings is a chore.

EX. we will have 20 gas loading, 25 gas pumping, 12 diesel pumping, 8
diesel
loading, 45 kero loading, 14 vgo pumping and 23 vgo loading for a total of
147. All scattered thur out the month that I have to track daily.

We used to do this with a tablet and pen but I have gotten Excel to do
alot
of it just not this part.

Sandy, thanks for the help the site was very helpful and I was able to get
most of what I needed to do except for one thing,

=SUMPRODUCT(('Product Log'!$B$3:$B$250="PG Asphalt")*('Product
Log'!$H$3:$H$250="P"))

with the formula above I am able to count "PG Asphalt" that we pump "P" on
one sheet and count them on another, I able to change the "L" in the formula
and count the loadings as well as changing the "PG" to another product and so
forth and so on (I have the product names (35 of them, above formula used by
changing the "names") in column A the counting goes in B and C depending on
the "P" or "L" from the other sheet but how do I count ODD NAMED products
that I do not have a name for in column A on my second sheet. On second sheet
I have the name "OTHER" just to count the odd once in a year handlings (could
be as many as 8 different products handled only a couple a times a year.) Is
there a way to get to count the odd names (er 123, pof 45, lube 125 are not
in column A because they may or may not be handled in the year and other odd
products are also handled) in one sheet to a specific cell in another without
having to specifically having to name them.

"tankerman" wrote:

In the spreadsheet I set up I have several columns/rows but only 3 of them
are giving me trouble. The 3 columns are PRODUCT , P/L (pump or load) , and
TOTALS that are giving me my trouble. We have several different products and
most are both loaded or pumped at different times during the month. I have to
keep a running total for daily, weekly, monthly and yearly for all product
handling at our facility.

How can I get my spreadsheet to count the number of individual
loading/pumping of each product. At the end of the sheet I SUM the column to
find the totals for all handlings in a month. Right now after I enter the
info in the row for a product I have to go up the sheet and count the # of
times the product had been pumped so far in the month and enter it in the end
row, this done for each product and loading or pumping. We have 20 plus
different products and all are either loaded or pumped during a give month so
trying to track all of the different product handlings is a chore.

EX. we will have 20 gas loading, 25 gas pumping, 12 diesel pumping, 8 diesel
loading, 45 kero loading, 14 vgo pumping and 23 vgo loading for a total of
147. All scattered thur out the month that I have to track daily.

We used to do this with a tablet and pen but I have gotten Excel to do alot
of it just not this part.

Sorry tankerman I am getting lost between your *second sheet* and *other
sheet* If you have a full list on sheet1 with an identical list on sheet2
with the exception that you have "Other" in place of some one off items then
you can use something like:

=SUMPRODUCT((Sheet1!A1:A16=A2)*(Sheet2!A1:A16="Oth er")*(Sheet1!B1:B16="P"))

Note first that you do not necessarily need to hard code the names into the
formula - you can just reference the cell. This formula counts the
instances where every cell in Sheet1 Column A is equal to the name in cell
A2 and the same ROW in Sheet2 had "Other in it and the same ROW in Sheet1
Column B has "P"

If that does not help then try a fuller explanation or send me privately an
example sheet.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"tankerman" wrote in message
...
Sandy, thanks for the help the site was very helpful and I was able to get
most of what I needed to do except for one thing,

=SUMPRODUCT(('Product Log'!$B$3:$B$250="PG Asphalt")*('Product
Log'!$H$3:$H$250="P"))

with the formula above I am able to count "PG Asphalt" that we pump "P" on
one sheet and count them on another, I able to change the "L" in the
formula
and count the loadings as well as changing the "PG" to another product and
so
forth and so on (I have the product names (35 of them, above formula used
by
changing the "names") in column A the counting goes in B and C depending
on
the "P" or "L" from the other sheet but how do I count ODD NAMED products
that I do not have a name for in column A on my second sheet. On second
sheet
I have the name "OTHER" just to count the odd once in a year handlings
(could
be as many as 8 different products handled only a couple a times a year.)
Is
there a way to get to count the odd names (er 123, pof 45, lube 125 are
not
in column A because they may or may not be handled in the year and other
odd
products are also handled) in one sheet to a specific cell in another
without
having to specifically having to name them.

"tankerman" wrote:

In the spreadsheet I set up I have several columns/rows but only 3 of
them
are giving me trouble. The 3 columns are PRODUCT , P/L (pump or load) ,
and
TOTALS that are giving me my trouble. We have several different products
and
most are both loaded or pumped at different times during the month. I
have to
keep a running total for daily, weekly, monthly and yearly for all
product
handling at our facility.

How can I get my spreadsheet to count the number of individual
loading/pumping of each product. At the end of the sheet I SUM the column
to
find the totals for all handlings in a month. Right now after I enter the
info in the row for a product I have to go up the sheet and count the #
of
times the product had been pumped so far in the month and enter it in the
end
row, this done for each product and loading or pumping. We have 20 plus
different products and all are either loaded or pumped during a give
month so
trying to track all of the different product handlings is a chore.

EX. we will have 20 gas loading, 25 gas pumping, 12 diesel pumping, 8
diesel
loading, 45 kero loading, 14 vgo pumping and 23 vgo loading for a total
of
147. All scattered thur out the month that I have to track daily.

We used to do this with a tablet and pen but I have gotten Excel to do
alot
of it just not this part.