Excel Help Please

lee1101

lee1101

lee1101

Soldato
Joined
18 Nov 2011
Posts
2,553
Location
Kent
Hi

I need some help with some formulas in excel please :D I am making a basic spreadsheet which records sub contractors earnings.

There are two sheets on the workbook, Input and Statements.

On the Input Sheet Column C has the subcontractor name and Column E has the Period Ending (05/05/2012 etc). Column F has the Gross Pay.

The statement sheet will basically provide a monthly summary for each employee.

The formula will be placed in C9 on the statement sheet. I need the formula in C9 to do the following...

Add up the gross pay column F (F1:F1000) on Input Sheet when Column C on the Input sheet (C1:C1000) are the same as C2 on the Statement Sheet AND when Column E on the Input Sheet (E1:E1000) are the same as C3 on the statement sheet.

So basically when the name and period matches then add up all of the gross pay. Sorry if its so confusing.

Basically will look like this

Name - Joe Bloggs
Period ended - 05/05/2012
Total Gross Pay - £xxxx

Any help would be great!
 
NutterzUK said:
Is there any chance you could post up a link to the file stripped down to just the bits you need?

I'm having trouble really understanding this.
Click to expand...

Yes of course, i can understand its a bit tricky to undestand without seeing the spreadsheet. I will get a link up when im at work tomorrow.

Thanks
 
=SUMIFS(Input!F1:F1000,Input!C1:C1000,Statements!C2,Input!E1:E1000,Statements!C3)


Try that, obviously substitute the Input! and Statements! sheet names with whatever you have called them :)
 
Last edited:
Code: 
=SUMPRODUCT(--(input!C1:C1000=Statements!C2),--(input!E1:E1000=Statements!C3),(input!F1:F1000))

That would work however it requires every cell within the ranges C1:c1000,E1:E1000 and f1:f1000 to be populated with valid data. ie, no '#N/A' errors in the name column. To get round that, try changing

Code: 
=LOOKUP(B9,Details!$A$2:$B$250)

to

Code: 
=iferror(LOOKUP(B9,Details!$A$2:$B$250),"")
 
Doesnt seem to be working :( I enter the formulas and they show in the cell as I entered them

CIS.jpg
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom