Sum of VLOOKUPS 1

[EcoWill]

Hi all - maybe I am not going about this in the right way, but i have repeating groups of categories with values associated with them. I can use VLOOKUP to get the values associated with each category separately but i want to sum the numbers of all the VLOOKUPs. How can one do this? Thanks,,

 

[EcoWill]

I can put it another way: I have in one column (A) Monday, Tuesday, Wednesday... etc, then repeating over and over again, Monday, Tuesday, Wednesday... Each day has a number associated with it in Column (B), like the first Monday has 235.24 but the second Monday is 562.57, etc. On another worksheet i want to sum up all of the Monday values, then a sum of all the Tuesday values, etc. I cannot use a pivot table to do this. Can i use the sum of VLOOKUPs and what would the syntax be? Thanks...

 

[SkipVought]

Hi,

Check out the SUMIF function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

I did that. I used this:

=SUMIF(P3595,(VLOOKUP(Criteria,B4:B595,15,FALSE)))

but it is returning all zeros even though column 15 has the values that I am trying to sum. Something in there is incorrect.

 

[SkipVought]

[tt]
=SUMIF(P3595,Criteria)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry...
[tt]
=SUMIF(B3:B595,Criteria,P3595)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Wow. Good job - It appears that that gives me real answers. Until I run a check-sum I won't know but it looks like this nailed it. I love Tek-tips!

 

[EcoWill]

How did that happen without any lookup function involved? If i do it on an adjacent spreadsheet in the workbook it appears to break down. (in other words I probably want to put these aggregates on another page but referencing and summarizing the data that is on the data page) Hope that makes sense.

 

[EcoWill]

Yep...if i attempt that formula from another sheet the numbers come out wrong. I don't get it.

 

[EcoWill]

MY MISTAKE. IT WORKS FINE. SORRY TO BOTHER YOU.

 

[lionelhill]

You might also like to consider pivot tables for this sort of aggregation.

 

[SkipVought]

a LOOKUP function, like VLOOKUP, INDEX, MATCH to name a few, returns a descrete value from a table based on a criteria. It can be a numeric value, a text value or a boolean value.

a AGGREGATION function, like SUMIF, COUNTIF to name a few, returns an aggregation from a a table based on a criteria. Excel 2007 has additional aggregation function that extend these capabilities to multiple criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EcoWill]

Got the message Skip. Thanks.