Posts by NBVC

• VLookup in different workbook

I'm wondering if you need to even reference wb1.... isn't that the workbook your macro is running from.... maybe your lookup value should just be

Code
1. myLookupValue = Sheets("PRJ").Range("Z7").Value
• VLookup in different workbook

Should the myLookupValue line be?

Code
1. myLookupValue = wb1.Range("Z7").Value
• Find the Difference of a Matching Pair

Can you give a sample of when my formula would not work? I am not understanding why it is important to include ticket number if the formula.

• Find the Difference of a Matching Pair

Although the formulae works, it is not quite what I was looking for. I had hoped of getting one which uses the Ticket No. as a criteria to find the partner.

Can you explain or show how we would use the Ticket No.? I only see evidence of the Ticket No. in the Main table on the left.

• Find the Difference of a Matching Pair

Assuming date in columns A:F and your first result in I3, try:

=SUMIFS(\$E\$2:\$E\$11,\$D\$2:\$D\$11,H3,\$C\$2:\$C\$11,"closing")-SUMIFS(\$E\$2:\$E\$11,\$D\$2:\$D\$11,H3,\$C\$2:\$C\$11,"Opening")

copied down

• Soccer League Table base on last 10 GAMES

Hi,

To sum the For's in column T, try this formula in AM3

=IF(\$AB3="","",SUMIFS(INDEX(\$T\$30:\$T\$1006,\$Z3):INDEX(\$T\$30:\$T\$1006,MATCH(10^10,\$Y\$30:\$Y\$1006)),INDEX(\$S\$30:\$S\$1006,\$Z3):INDEX(\$S\$30:\$S\$1006,MATCH(10^10,\$Y\$30:\$Y\$1006)),\$AB30))

and this in AN3 for the the Away's

=IF(\$AB3="","",SUMIFS(INDEX(\$V\$30:\$V\$1006,\$Z3):INDEX(\$V\$30:\$V\$1006,MATCH(10^10,\$Y\$30:\$Y\$1006)),INDEX(\$S\$30:\$S\$1006,\$Z3):INDEX(\$S\$30:\$S\$1006,MATCH(10^10,\$Y\$30:\$Y\$1006)),\$AB30))

both copied down

• INDEX-MATCH to return FALSE if there is no match instead of #N/A

Add a check for blank first...

=IF('SA-Question'!B3="",FALSE,IFNA(.....))

• excel formula needed to lookup values

Then perhaps, in B7 try: =ROUNDUP(B6/12,0)

then final formula, try: =SUMIFS(INDEX(output!\$D\$2:\$R\$6,,MATCH("YR_"&B7,output!\$D\$1:\$R\$1,0)),output!\$A\$2:\$A\$6,B9,output!\$B\$2:\$B\$6,B8)

• excel formula needed to lookup values

I don't get your number 2 request. What would be the expected result in B7 given the current parameters in your sheet?

Also, what is the expected final result?

• SUMIFS based on two criteria with different size ranges

I get 558.. see attached.

Files

• Sample1.xlsx

• SUMIFS based on two criteria with different size ranges

Hi,

Can you please indicate with links where else you have posted this question? We would like to be able to see if/how other helpers have replied. (It's also one of our rules).

In the meantime try:

=SUMPRODUCT(--(ISNUMBER(MATCH('MTN LIST'!E3:E120,H28:H45,0))),--('MTN LIST'!D3:D120<>""),'MTN LIST'!H3:H120)

• Calculate amount by looking up matching currency and rate of exchange

Change the header at D1 to "Rate", then in new column use:

=IF([@Curr]="USD",[@Total],IF([@Curr]="RSD",[@Total]/[@Rate],IF([@Curr]="EUR",[@Total]*[@Rate],"")))

Try:

• Countif not working

The only way we can say for sure is if you post the workbook so we can see where you've gone wrong.

One thing to check is make sure that B5 contains a numeric value and that it is not formatted as text or is blank.

• Conditional Formatting Forumal

We don't see a sample.

Try these conditional format formulas, assuming you've selected from rows 2 down...

Red: =AND(\$E2<>"",\$E2<TODAY())

Orange: =AND(\$E2>=TODAY(),\$E2<=EOMONTH(TODAY(),0))

Blue: =AND(\$E2>EOMONTH(TODAY(),0),\$E2<=EOMONTH(TODAY(),1))

• Turn SUMIFS negative value into 0 (ZERO)

or

=MAX(0,Sumifs('GL Detail YTD'!Q:Q,'GL Detail YTD'!P:P,\$D20,'GL Detail YTD'!V:V,\$J\$7))

• Combining multiple .Find Criteria into a single combined .Find Criteria

Hi Jonathan,

Thanks for reviving this and trying to help me solve this simply.

It seems to have worked. Thanks.

• Indexing Across Multiple Tabs

You're welcome.

• Indexing Across Multiple Tabs

Try this Array* formula in C4 of 'Overview'

=IFERROR(IFERROR(INDEX('Jan - June'!\$C\$3:\$I\$3,SMALL(IF(('Jan - June'!\$C\$3:\$I\$3>=\$E\$1)*(('Jan - June'!\$C\$3:\$I\$3<=\$H\$1)*('Jan - June'!\$C4:\$I4=1)),COLUMN('Jan - June'!\$C\$3:\$I\$3)-COLUMN('Jan - June'!\$C\$3)+1),COLUMNS(\$C4:C4))),INDEX('Jul - Dec'!\$C\$3:\$I\$3,SMALL(IF(('Jul - Dec'!\$C\$3:\$I\$3>=\$E\$1)*(('Jul - Dec'!\$C\$3:\$I\$3<=\$H\$1)*('Jul - Dec'!\$C4:\$I4=1)),COLUMN('Jul - Dec'!\$C\$3:\$I\$3)-COLUMN('Jul - Dec'!\$C\$3)+1),COLUMNS(\$C4:C4)-\$B4+COUNTIFS('Jan - June'!\$C\$3:\$I\$3,">"&\$E\$1,'Jan - June'!\$C\$3:\$I\$3,"<="&\$H\$1,'Jan - June'!\$C4:\$I4,1)))),"")

Then copy across and down the table.

[arf]*[/arf]

• SumProduct fails to accept "*"

Maybe something like this might work?

Subbing (Encounters[Grant]=\$L\$3) for (IF(\$L\$3="*",LEN(Encounters[Grant])>=0,Encounters[Grant]=\$L\$3))