Re: lookup name and sum corresponding values

Try SUMIF

For example if you have names in column A and values in B then this formula will sum the values for John

=SUMIF(A:A,"John",B:B)

or you can put John is a cell, e.g. D2 and use

=SUMIF(A:A,D2,B:B)

Re: Conditional SUMPRODUCT - single criteria, multiple SUMPRODUCTs

You can pick out the Fequency columns like this, e.g. for sum of "freq" try this in Q3 copied down

=SUMPRODUCT((\$A\$3:\$A\$14=\$J3)*(LEFT(\$B\$2:\$F\$2,4)="Freq"),\$B\$3:\$F\$14)

For "means" in R3 copied down

=SUMPRODUCT((\$A\$3:\$A\$14=\$J3)*(LEFT(\$B\$2:\$F\$2,4)="Freq"),\$B\$3:\$F\$14,\$C\$3:\$G\$14)

Re: Conditional Format Multiple Criteria

Try

=COUNTIFS(\$H\$2:\$H\$6217,\$H2,\$N\$2:\$N\$6217,"<>"&\$N2)>0

Re: Subtract calendar months

You can use EDATE with negative numbers, for the requirement for the last day of the month try like this

=EDATE(A1+(DAY(A1+1)=1),-6)-(DAY(A1+1)=1)

Note that EDATE requires Analysis ToolPak to be enabled

Re: Adding matches to INDEX MATCH

I believe the formula should work - it will return #N/A if you don't have a row where all 3 criteria match, are you sure that row exists?

Re: Adding matches to INDEX MATCH

I don't believe that will work. If you need a match in all 3 columns try

=INDEX(\$AC\$3:\$AC\$16067,MATCH(1,INDEX((\$B16069=\$B\$3:\$B\$16067)*(\$AA16069=\$AA\$3:\$AA\$16067)*(\$AB16069=\$AB\$3:\$AB\$16067),0),0))

Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

You can do this with a SUMPRODUCT formula. Might not be the best approach for you but this version of your formula will treay blanks or * in the criteria range as an "any value" criteria

=SUMPRODUCT(--(D4:D11=IF(OR(G4={"","*"}),D4:D11,G4)),--(E4:E11=IF(OR(H4={"","*"}),E4:E11,H4)),--(F4:F11=IF(OR(I4={"","*"}),F4:F11,I4)))

Note that SUMPRODUCT lets you have up to 255 arrays in Excel 2007 (but it's limited to 30 in Excel 2003)

COUNTIFS, btw has a limit of 127 range/criteria pairs

Re: How do i get a cell to read a text string ...

Assuming you have the numbers in one column and the text strings in a column to the right, e.g. numbers in Y2:Y100 and text in Z2:Z100 then you can use VLOOKUP, e.g.

=VLOOKUP(A1,Y\$2:Z\$100,2,0)

Re: Lookup with Multiple Criteria

The conditions return arrays of TRUE/FALSE values. When you divide 1 by TRUE you get 1, otherwise an error [#DIV/0!] so the lookup range returns an array of errors and 1s. Lookup value, in fact, can be any number >= 1 so it could be 1, 200 or 4.5 the formula will still work (2 is often used). When lookup value is > than any value in the lookup range it just matches with the last number, in this case the last 1 representing the last row where the 2 conditions are met. you can add more conditions easily, e.g.

=LOOKUP(2,1/(F1=B2:B13)/(G1=A2:A13)/(H1=D2:D13),C2:C13)

Re: Lookup with Multiple Criteria

Sorry, when I said "Product in G1" I meant grade, so if G1 contains Product2 and F1 contains Oct 22/2010 then the formula I suggested should return the value of 4.

Re: Index/Match formula returns #N/A

What formula do you have in J2? Some functions (LEFT, RIGHT, MID and others) always return text results (even if the results look numeric), this might cause the MATCH to fail. If the data is of this type (numeric) then try adding +0 to J2 to force conversion to a number, i.e.

=INDEX('SFDC All Accounts'!\$A\$2:\$Z\$500,MATCH(J2+0,'SFDC All Accounts'!\$G\$2:\$G\$500,0),8)

Note: this won't work for text values, if J2 is text then check that you don't have trailing or leading spaces or other characters that might make the MATCH fail

Also I notice for that specific formula, at least, you are looking up a value in column G and returning one from H so VLOOKUP is another possibility, i.e.

=VLOOKUP(J2+0,'SFDC All Accounts'!\$G\$2:\$H\$500,2,0)

but, in terms of match criteria VLOOKUP works in the same way as MATCH so you'll likely have the same issues with that......

Re: Excel 2007 - Bi-annualy bonus pool formula

Hello fchachar,

As this is an assignment I won't give you a full answer but try looking at SUMIF function

Re: How to look up a Value in a spreadsheet when I have Unique identifier in two colu

Let's say you had the product in G1 you could use this formula

=LOOKUP(2,1/(F1=B2:B13)/(G1=A2:A13),C2:C13)

Re: Schedule of hours

If you have the days of the week in A2:A8 and the start of each shift in B2:B8, end of shift in C2:C8 then you can use this formula in D2 copied down

=IF(COUNT(B2,C2),MOD(C2-B2,1),"")

That will give you the total for each day, then you can sum that at the bottom.

All cells should be formatted in required time format. Weekly sum cell should be formatted as [h]:mm to show hours over 24 if necessary

Re: Schedule of hours

What's your input data? Are you supplying a start time/date and an end time/date...or just dates? Do you want to count those hours for every day or only for certain days e.g. Monday to Friday. An example with expected result would be helpful, thanks

Re: Highlight row if data in range is the min. or the max.????????

Select the whole range A8:M13 then apply this formula in conditional formatting

=\$L8=MIN(\$L\$8:\$L\$13)

choose red format

repeat for the maximum with MAX in place of MIN. Note the \$ signs - they need to be as I have them here

Re: Sum Using Criteria

Quote from AAE;527339

=SUMPRODUCT(--(\$A\$2:\$A\$13="E")*--(\$B\$2:\$B\$13="P1"),(\$C\$2:\$C\$13))

I don't like the syntax much, you never need to use * and --

preferable to use either

=SUMPRODUCT((\$A\$2:\$A\$13="E")*(\$B\$2:\$B\$13="P1"),\$C\$2:\$C\$13)

or

=SUMPRODUCT(--(\$A\$2:\$A\$13="E"),--(\$B\$2:\$B\$13="P1"),\$C\$2:\$C\$13)

Re: Convert decimal minutes into a hours minutes and seconds over three cells

If figure in A1 might be higher than 1440 (i.e. more than 24 hours) you might need to modify Mike's suggestion for hours, i.e.

=TEXT(A1/24/60,"[h]")+0

note the square brackets

for minutes and seconds you could also use MINUTE and SECOND functions, i.e.

=MINUTE(A1/1440) and

=SECOND(A1/1440)

Re: Formula that returns result causes IFERROR and ISERROR to evaluate as error

Hello Deepak,

Frankly I'm not sure why those formulas don't work but I'll propose an alternative.....

Your solution involves finding the specific range to evaluate using INDIRECT, OFFSET, ADDRESS etc. That has some advantages in that the range to evaluate - eventually - is small....but you lose a lot of that advantage with some of those volatile functions (and especially SUMPRODUCT) so I'd suggest this shorter formula to give the same result

=LARGE(IF((INDEX(TestData,,1)=\$B12)*(INDEX(TestData,,2)=\$C12)*(INDEX(TestData,,3)=\$D12)*(INDEX(TestData,,6)=\$A12),INDEX(TestData,,5)),AC1)

This needs to be confirmed with CTRL+SHIFT+ENTER

When I wrap that in IFERROR I still get the same result

Try that on your real data and see if it works for you........

Re: Date Formating problem

I'm not clear if this is just a problem with the years or another issue - if the result you get is 01-Jan-1900 that's just a result of zero (but formatted as a date). Are you in the UK? (do you have a default date format of dd/mm/yy?)

If all the data has 2 digits for the day and month (with leading zeroes if necessary) then perhaps this formula will work better

=MID(A1,FIND("-",A1)-2,8)+0

If that doesn't work can you give a few examples where the formula(s) give the wrong results - what's the exact data, what result do you get....