Can you use sumif or V lookup to total elements in one column based on values in another column?

[Jjagz]

Good morning, I am trying to use sumif and/or vlookup to solve my problem in my worksheet and there are too many variables for my expertise. The data is set up like this:

[TABLE="width: 343"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 4"][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Eng Hours
[/TD]
[TD]Survey Hours
[/TD]
[TD]Rate
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]200
[/TD]
[TD]1600
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]150
[/TD]
[TD]750
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]125
[/TD]
[TD]250
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]190
[/TD]
[TD]380

[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD]2980
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
I want to create a formula that will total column D for Eng hours and for Survey Hours on another sheet

Eng Total = $1850.00
Survey Total $1130.00

Is this possible to do the way my budget worksheet is already set up?

Thanks in advance!

Jjagz

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Your column D shows the total per record, regardless Eng or Survey... Such is confusing in light of what you seem to be looking for...

Let A:C of Sheet1 house the data.

In A1 of Sheet2 enter:

Eng

In A2 of Sheet2 enter:

Survey

Now in B1 of Sheet2 enter and copy down:

=SUMPRODUCT(Sheet1!$C$2:$C$6,INDEX($A$2:$B$6,0,MATCH(A1&"*",Sheet1!$A$1:$B$1,0)))

Note that the table does not list a rate in C6.

Hope this helps.

 

[Jjagz]

Thank you for your response! I was hoping to avoid recreating my budget worksheet - After more searches, I discovered it actually works to use a simple if/sumif - I just wasn't doing it correctly.

=IF(B6="Eng",SUMIF('Budget Hours by Phase'!$D$7:$D$28,">0",'Budget Hours by Phase'!$G$7:$G$28),SUMIF('Budget Hours by Phase'!$E$7:$E$28,">0",'Budget Hours by Phase'!$G$7:$G$28))

Thanks again!

 

[Aladin Akyurek]

Thank you for your response! I was hoping to avoid recreating my budget worksheet

The suggestion doesn't recreate anything...