add number of duplicate fields in a row or column and have total

I have a log of jobs done on a photocopier, and need to bill people for what
they have used.

the log is very long and have the user name and the number of colour and
number of Black and White copies they have done.

I need to know how much colour and how much black and white jobs have been
done by each user.

hope someone know a way thanks

or am I better to use Access ?

J

=SUMPRODUCT(($A$1:$A$20="Bill")*($C$1:$C$20)*ISNUMBER(FIND({"C","B"},$B$1:$B
$20))*{3,2})

THe assumptions made

People in A1:A20
Type in B1:B20, C for colour, B for B&W
Quantity in C1:C20

The {3,2} is the different rates for Colour and B&W

--

HTH

RP
(remove nothere from the email address if mailing direct)


"thanks for the help mate" <123> wrote in message
news:ukC%[email protected]...
> I have a log of jobs done on a photocopier, and need to bill people for
what
> they have used.
>
> the log is very long and have the user name and the number of colour and
> number of Black and White copies they have done.
>
> I need to know how much colour and how much black and white jobs have been
> done by each user.
>
> hope someone know a way thanks
>
> or am I better to use Access ?
>
> J
>
>

this is an example of the log that is saved from the photocopier.

the fields do not have to be in this order we can have user first if this
make it easier.

J



Job Log
Number Of Pages, Number Of Copies, Number Of Pages, Total Number Of Color
Pages Printed, Total Number Of Black And White Pages Printed, User

1, 1, 1, 0, 1, Andy
1, 1, 1, 0, 1, Andy
1, 1, 1, 1, 0, Administrator
1, 1, 1, 1, 0, John
1, 0, 0, 1, 0, Andy
1, 1, 1, 0, 1, Andy
1, 1, 1, 1, 0, Administrator
1, 1, 1, 1, 0, Administrator
1, 0, 0, 1, 0, Mick
1, 1, 1, 1, 0, John
1, 1, 1, 0, 1, John
1, 1, 1, 1, 0, John
1, 1, 1, 0, 1, John
1, 1, 1, 0, 1, John
1, 1, 1, 1, 0, Administrator
1, 1, 1, 1, 0, Administrator
1, 0, 0, 1, 0, Andy
1, 1, 1, 1, 0, Andy
1, 1, 1, 0, 1, Andy
1, 1, 1, 1, 0, Administrator
1, 1, 1, 0, 1, Andy
1, 1, 1, 1, 0, Administrator
1, 1, 1, 0, 1, Mick
1, 1, 1, 1, 0, Administrator
1, 1, 1, 1, 0, Administrator
1, 0, 0, 1, 0, Andy
1, 1, 1, 1, 0, John
1, 1, 1, 0, 1, John
1, 1, 1, 1, 0, Administrator
1, 0, 0, 1, 0, Mick
1, 1, 1, 1, 0, John
1, 1, 1, 0, 1, Andy
1, 1, 1, 1, 0, Administrator
1, 0, 0, 1, 0, John
1, 1, 1, 1, 0, John
1, 1, 1, 0, 1, John











"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(($A$1:$A$20="Bill")*($C$1:$C$20)*ISNUMBER(FIND({"C","B"},$B$1:$B
> $20))*{3,2})
>
> THe assumptions made
>
> People in A1:A20
> Type in B1:B20, C for colour, B for B&W
> Quantity in C1:C20
>
> The {3,2} is the different rates for Colour and B&W
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "thanks for the help mate" <123> wrote in message
> news:ukC%[email protected]...
>> I have a log of jobs done on a photocopier, and need to bill people for
> what
>> they have used.
>>
>> the log is very long and have the user name and the number of colour and
>> number of Black and White copies they have done.
>>
>> I need to know how much colour and how much black and white jobs have
>> been
>> done by each user.
>>
>> hope someone know a way thanks
>>
>> or am I better to use Access ?
>>
>> J
>>
>>
>
>

I think that all you need then is

=SUMIF(F:F,"Andy",D:D)*3+SUMIF(F:F,"Andy",E:E)*2

where 3 and 2 are still the colour and B&W weights

--

HTH

RP
(remove nothere from the email address if mailing direct)


"thanks for the help mate" <123> wrote in message
news:[email protected]...
> this is an example of the log that is saved from the photocopier.
>
> the fields do not have to be in this order we can have user first if this
> make it easier.
>
> J
>
>
>
> Job Log
> Number Of Pages, Number Of Copies, Number Of Pages, Total Number Of Color
> Pages Printed, Total Number Of Black And White Pages Printed, User
>
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 1, 0, John
> 1, 0, 0, 1, 0, Andy
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 1, 0, Administrator
> 1, 0, 0, 1, 0, Mick
> 1, 1, 1, 1, 0, John
> 1, 1, 1, 0, 1, John
> 1, 1, 1, 1, 0, John
> 1, 1, 1, 0, 1, John
> 1, 1, 1, 0, 1, John
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 1, 0, Administrator
> 1, 0, 0, 1, 0, Andy
> 1, 1, 1, 1, 0, Andy
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 0, 1, Mick
> 1, 1, 1, 1, 0, Administrator
> 1, 1, 1, 1, 0, Administrator
> 1, 0, 0, 1, 0, Andy
> 1, 1, 1, 1, 0, John
> 1, 1, 1, 0, 1, John
> 1, 1, 1, 1, 0, Administrator
> 1, 0, 0, 1, 0, Mick
> 1, 1, 1, 1, 0, John
> 1, 1, 1, 0, 1, Andy
> 1, 1, 1, 1, 0, Administrator
> 1, 0, 0, 1, 0, John
> 1, 1, 1, 1, 0, John
> 1, 1, 1, 0, 1, John
>
>
>
>
>
>
>
>
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> >
=SUMPRODUCT(($A$1:$A$20="Bill")*($C$1:$C$20)*ISNUMBER(FIND({"C","B"},$B$1:$B
> > $20))*{3,2})
> >
> > THe assumptions made
> >
> > People in A1:A20
> > Type in B1:B20, C for colour, B for B&W
> > Quantity in C1:C20
> >
> > The {3,2} is the different rates for Colour and B&W
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "thanks for the help mate" <123> wrote in message
> > news:ukC%[email protected]...
> >> I have a log of jobs done on a photocopier, and need to bill people for
> > what
> >> they have used.
> >>
> >> the log is very long and have the user name and the number of colour
and
> >> number of Black and White copies they have done.
> >>
> >> I need to know how much colour and how much black and white jobs have
> >> been
> >> done by each user.
> >>
> >> hope someone know a way thanks
> >>
> >> or am I better to use Access ?
> >>
> >> J
> >>
> >>
> >
> >
>
>