Formula

Godbless

New Member
Joined
Aug 13, 2007
Messages
7
Hi

Does anyone know what formula is needed to get excel to calculate all A rates seperate from B rates and C rates example 10 A rates @ 50.00 9 B rates @ 40.00 and 6 C rates @ 30.00 so although the rates could be mixed up (not following in any particular order the result which I would like to appear on a separate spreadsheet would be A = 500.00 B = 405.00 C = 180.00 then to get them to total up = £1085.00

Hope this makes sense.
A 50.00
B 40.00 then separate sheet with answer a 100.00 b 40.00 c 60.00
A 50.00
C 30.00
C 30.00
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Welcome to the board.

I think you're looking for Sumif

=SUMIF(A:A,"A",B:B) - would sum column B where column A = A
=SUMIF(A:A,"B",B:B) - would sum column B where column A = B
=SUMIF(A:A,"C",B:B) - would sum column B where column A = C
 
Upvote 0
Lots of ways. A pivot table will do this for you.

Although if you have 9 B rates @ 40.00, that is presumably 360.00, not 405.00.
 
Upvote 0
formula

Hi

Sorry i never made myself clear

I have 4 columns in column G I have all the grades which could vary from B C A. In Column H Time in column I rate which differs for each grade and in J Amount.

What I am after is on my summary sheet it calculates hours and mins of all (A's, B's and C's in say column F. In column G counts the amounts of A's B's and C's separate rows and in column H the amount of time X' the Rate of each Grade on separate rows.

If you can solve this for me I will be so please
 
Upvote 0
SHEET 1 COLUMN
G H I J
GRADE H/M RATE AMOUNT
ROW
A 0:24 50.00 £20.00
B 1:12 40.00 £48.00
C 2:30 20.00 £50.00
A 0:12 50.00 £10.00
B 0:24 40.00 £16.00
C 2:00 20.00 £40.00
C 3:00 20.00 £60.00
B 0:24 40.00 £16.00
SUMMARY ON NEW SPREADSHEET
COLUMNS
D E F G
hrs/mins No amount grade
Preparation rows 0:36 2 30.00 A
2:00 3 80.00 B
7:30 3 150.00 C
In the new spreadsheet (the summary sheet) I would like a formula to be set up so each time I enter the either work done by a b grade person or c garde person and a grade person at whatever hours or mins it updates the individua rows in in the summary sheet of each grade worker the hrs/ms the number and amount. I know I should use sumif but not sure how to apply it as I have never used it before. Should the formula be used on the summary sheet to grabe the information from sheet 1 g,h,i,j which is the sheet I am inputting the information.

Sorry
 
Upvote 0
Hi and God bless you!

This what I get using simple PIVOT TABLE

Suggest that you use PT to get exactly what you need:
Book1
ABCDEFGHIJK
1GRADEH/MRATEAMOUNT
2A0:245020
3B1:124048Data
4C2:302050GRADESumofH/MCountofRATESumofAMOUNT
5A0:125010A0:36230
6B0:244016B2:00380
7C2:002040C7:303150
8C3:002060GrandTotal10:068260
9B0:244016
10
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top