Not sure if this is a countif or sumif question, any assistance is appreciated

[dchau]

Hi my name is Deb, been on the site before and can't express how helpful it's been, you are wonderful people. So my new dilemma is this:

[pre]

column c    trucker's name
column g    internal cost
column h    external cost
if it's an internal cost it's only in the internal cost column g, if it's an external cost it's in internal and external.  I need to add the trucker's costs separately for each column, i.e. internal and external.


example

trucker                  internal cost             external cost
Joe                          $400.00
Joe                          $400.00                     $400.00
Joe                          $500.00                     $500.00

[/pre]
I need a formula that can tell me his internal cost is $400.00 only and his external is $900.00 only. Can you help determine how to do this? Any assistance is appreciated. Thank you

 

[Luke M]

INTERNAL:

Since it's the same cost in both columns, you could do:

=SUMIF(C:C,"Joe",G:G")-SUMIF(C:C,"Joe",H:H)


If it's not always the same, could do:

=SUMPRODUCT((C2:C1000="Joe")*(H2:H1000="")*(G2:G1000))


External should just be:

=SUMIF(C:C,"Joe",H:H)

 

[dchau]

great thank you so much I'll try that. thank you again.

 

[Debraj]

Hi Deb,


Assuming

A1:A20 - > Name

B1:B20 - > Internal data

C1:c20 - > External data


then In In E2 write "JOE" & in F2 write "Internal / External"

then in G2 use below formula..


=SUMIFS($B$2:$B$20,$C$2:$C$20,

VLOOKUP(F2,{"Internal","=";"External",">0"},2,0),$A$2:$A$20,E2)


PS: If you don't have 2007 +, still we can manage it ..

PPS: am I talking to myself..


Regards,

=DEC2HEX(3563)

 

[dchau]

it worked thank you so much