3 Conditions "Earliest Date" , "Latest Date", "String"

[xijiangwoo]

Hello Everyone !

I am a total noob to Excel. I've been slowly teaching myself the past 2 weeks. I have the below formula working with multiple conditions.

=SUMPRODUCT((CompletionDate>=Z15)*(CompletionDate<= AA15)*Engraving)

The CompletionDate is a range column of dates, Z15 is the cell with the earlier date and "AA15" is cell with the later date. If those conditions are met, it will show a total from the "Engraving" range column and display the total in cell "Z25".

I would like to add a criteria to search a column (Person) of 4 different names and display the total from "Mike" (One of the four names).

Am I barking up the wrong tree here?

Any help is grealty appreciated !!!

Xi

 

[konew1]

You can add more criteria, like
=SUMPRODUCT((CompletionDate>=Z15)*(CompletionDate<= AA15)*(Person="Mike")*Engraving)
Using sumproduct I would suggest
=SUMPRODUCT(--(CompletionDate>=Z15),--(CompletionDate<= AA15),--(Person="Mike"),Engraving) which is the same thing, but I understand to be more efficient.
What version of excel do you have? =SUMIFS( would be better if you have Xl2010+ (and maybe xl2007)

 

[xijiangwoo]

Thank you. I am working with "2010". I couldn't figure out how to use sumifs, so I found a sumproduct formula to help me get started.

 

[Peter_SSs]

Welcome to the MrExcel board!

Suppose you put "Mike" in cell AB15 then the SUMIFS version would be

=SUMIFS(Engraving,CompletionDate,">="&Z15,CompletionDate,"<="&AA15,Person,AB15)

 

[xijiangwoo]

Thanks Peter !

What if I wanted to add multiple named ranges? "engraving" "ETCH" "Cutout"

I'm trying to read the help files as well.

Thanks for all of the input !

Xi

 

[Peter_SSs]

What if I wanted to add multiple named ranges? "engraving" "ETCH" "Cutout"

If that means you want to sum those 3 named ranges when the dates are between earliest & latest and Person = Mike, then I would return to SUMPRODUCT.

=SUMPRODUCT(--(CompletionDate>=Z15),--(CompletionDate<= AA15),--(Person=AB15),Engraving+ETCH+Cutout)

If your data is in a 'Table', then you may also find this thread useful.

 

[xijiangwoo]

Thank you Everyone ! Your help was greatly appreciated. Thanks again !

Xi

 

[Peter_SSs]

Thank you Everyone ! Your help was greatly appreciated. Thanks again !

Xi

Thanks for letting us know.