VLOOKUP to search text between dates

I basically want to search certain text between certain dates and once the text is found, want to add all values of corresponding cells.

Attached sheet has all the info on.

This forum is luckily the place to ask these questions.


Thanks for all the help NBVC....

Something like:

=SUMIFS(Sheet2!C1:C4,Sheet2!B1:B4,"Transaction Charge",Sheet2!A1:A4,">=20100508",Sheet2!A1:A4,"<=20100512")

you can replace the criteria with cell references.

e.g

=SUMIFS(Sheet2!C1:C4,Sheet2!B1:B4,X1,Sheet2!A1:A4,">="&Y1,Sheet2!A1:A4,"<="&Z1)

where X1, Y1 and Z1 hold the respective search criteria

if you are going to drag the formula down, etc... make the ranges absolute... (i.e. add $ signs)

Somehow the formula wasn't accepted the first time...

I just hit F2 and hit Enter and it worked.

But note, that in Sheet2, there are extra spaces after Transaction Charge in B1 and B3... please remove those spaces so that you get an accurate result.

So are you working in 2003 or 2007?

Try:

=SUMPRODUCT(C1:C4,--(B1:B4="TRANSACTION CHARGE"),--(A1:A4>=20100508),--(A1:A4<=20100516))

See my last post above for an alternative... the formula I initially gave was under the assumption your were using 2007... SUMIFS is only for 2007 and later versions.

That didn't make much sense??

If you want not to show negative results and show 0 instead, then:

=MAX(0,SUMPRODUCT(C1:C4,--(B1:B4="TRANSACTION CHARGE"),--(A1:A4>=20100508),--(A1:A4<=20100516)))

if you want to add up only positive numbers, then

=SUMPRODUCT(C1:C4,--(B1:B4="TRANSACTION CHARGE"),--(A1:A4>=20100508),--(A1:A4<=20100516),--(C1:C4>=0))

Is it one of those?

so then:

=ABS(SUMPRODUCT(C1:C4,--(B1:B4="TRANSACTION CHARGE"),--(A1:A4>=20100508),--(A1:A4<=20100516)))

??

Put the ABS() around the SUMIFS formula I gave you initially.

Note:

The formula as is, will also work in 2007.. so you don't have to change it if you do not want to.

What do you mean by closest match to "Transaction Charge"

What are the possibilities?

Try:

=ABS(SUMPRODUCT(C1:C4,--ISNUMBER(SEARCH("TRANSACTION CHARGE",B1:B4)),--(A1:A4>=20100508),--(A1:A4<=20100516)))

this will cater to the cells in column B containing the string anywhere within the cells...