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

  • Thread starter Thread starter thanks for the help mate
  • Start date Start date
T

thanks for the help mate

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)
 
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
 
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)
 
Back
Top