• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

multiple conditions,ranges in excel

D

dchau

New Member
I'm kind of new working with various formulas, like sumif, etc I am hopeful this is an easy question for someone. I have 4 columns with information, example:


truck# driver# loadinfo hours

190 brian logs 5

190 brian shuttle 4

190 brian pulp 2

190 brian post .50

190 brian pre .25


I need to get a total of hours for Brian for logs,shuttle, pulp but not pre or post into one cell only


so c250 would equal 11 i've tried various ranges, criteria and it's not coming out right.

your assistance will be very appreciated.
 
Hi dchau,


Welcome to Chandoo_Org forums.


Assuming your data is in cells C2:D6 try this formula:


=SUMPRODUCT((B2:B6="brian")*ISNUMBER(FIND(C2:C6,{"logs","shuttle","pulp"},1))*D2:D6)


PS: Request to Mods. Some posts got stuck in "spam" filter, please delete 'em.
 
It is part of FIND function arguments. 1 refers to start_num.

=FIND(find_text,within_text,start_num)


1. FIND searches for 3 Keywords as you have stated.

2. If it finds match then ISNUMBER turns into boolean TRUE/FALSE result.

3. Then conditional result for brian and step 2 are multiplied together.

4. Results at step 3 are then multiplied to hours and give us desired SUM.


Use "Evaluate Formula" to understand functioning.
 
=SUMPRODUCT((C2:C202="brian")*ISNUMBER(FIND(D2:D202,{"logs","shuttle","pulp"},1))*F2:F202)


I'm sorry but it didn't work, not sure if it's because of the 1 or not.


I dont know what you mean by Request to Mods. Some posts got stuck in "spam" filter, please delete 'em??? is this something I need to do? if so, where do I do this?
 
ok so Brian is in column c2:c202

and logs,shuttle,pulp are in d2:d202

and the sum that I need are in f2:f202


looks correct but not working
 
Hi Dchau,


Try to use () at proper place.. :)


Regards,

Deb


BTW.. Try this One too..

=SUMPRODUCT((c2:c202="Brian")*(d2:d202<>"Pre")*(d2:d202<>"Post"),f2:f202)
 
You are THE BEST, the other one worked, thank you, thank you, thank you, you are the goddess of EXCEL.
 
oops..sorry..

Your formula (I mean shrivallabha's) is also correct and its working for me..


Code: 
=SUMPRODUCT((C2:C202="brian")*ISNUMBER(FIND(D2:D202,{"logs","shuttle","pulp"},1))*F2:F202)


You are from Vermont USA.. so I don't hope.. ",/;" need to swap.. but plz try if still not working..


Regards,

Deb
 
Hi Dchau,


^&%&% the suggestion of changing ,(comma) to ;(semicolon)..

Just change FIND to SEARCH.. :) in Shri's formula..


Or if you have enough time then change All

"logs","shuttle","pulp" to "LOGS","SHUTTLE","PULP"..in D2:D202


Regards,

Deb
 
You must log in or register to reply here.
Back
Top