Sumif using Named Ranges

[deletedalien]

good morning guys,


i think this should be an easy one but i just cant figure it out

ok so i have dates in column A:A and data in column L:L

i want to do named ranges by dates A30:L80 "Jul18" so that if i need to do a lookup only on that range i can just do =vlookup(B2,Jul18,11,0) and get what i want

BUT

if i want to add the total of the data range inside the named range "Jul18" only in column L i thought i could do something like
=sumif(Jul18,"07/18/11",L:L)

but appparently that brings back an incorrect number,

do you guys know of a workaround?


Thanks in advance

 

[Yahya]

in your formula
=sumif(Jul18,"07/18/11",L:L)

Refers to A30:L80
try to change it to A:A

 

[mikerickson]

Try
=SUM(INDEX(Jul18, 0, 12))

Which returns the sum of the values in the 12th column of Jul18.

 

[deletedalien]

in your formula
=sumif(Jul18,"07/18/11",L:L)

Refers to A30:L80
try to change it to A:A

i would but if i did that then it would sum up the total of all dates (all column A:A)

thats why im using named ranges, to separate the data by dates.

so in this scenario i want excel to sum up only the total of the sum in column L:L BUT from the row that begins in the named range to the last row in the named range,

in other words only the total of Jul18 (Named range "Jul18" A30:L80)

 

[Andrew Poulsom]

Try:

=SUMIF(INDEX(Jul18,,1),"07/18/11",INDEX(Jul18,,COLUMNS(Jul18)))

 

[deletedalien]

Try:

=SUMIF(INDEX(Jul18,,1),"07/18/11",INDEX(Jul18,,COLUMNS(Jul18)))

GOOD GOD MAN!!!!

YOU'RE A GENIUS!!!!!!!!!

thank you x1000000!!!