Vlookup??

**ricoandquesa** · 10-24-2007, 10:27 PM

I have been trying to work out another formula to search cells of a column, and when a cell matches the a value it adds the corresponding cell to achieve one total. I am not sure if I need to do a VLOOKUP or not??

So if there is the value Local in column E and it is in e24 the corresponding cell j24 is added to one total, in the end any cells in column j correspnding with cells with the value Local in column E will all be added together. If there is a different value Long then they will be added together - I hope this makes sense!

The formula that I have so far is =IF(E24=Local,SUM(J24:J89),0) but it only works because I have to correct range of cells selected. I want the range of cells to be expanded to include anything in that columnn. I tried =IF(E24=Local,SUM(J1:1000),0) but that just added every thing in column J.

I dont think it was a very good attempt.

**Paul** · 10-24-2007, 10:37 PM

Hi ricoandquesa,

I think what you're looking for is a SUMIF function, for example:

=SUMIF(E:E,"local",J:J)

This will search column E for all instances of "local" and sum the values in column J only where "local" is found.

**ricoandquesa** · 10-24-2007, 11:04 PM

Heya - I think I was trying to get too complicated - would the same formula if I subsituted SUMIF with COUNTIF?
Thanks Quesa

**Paul** · 10-24-2007, 11:14 PM

Hi again,

So are you trying to just count the number of times 'Local' appears in column E? Or are you wanting to sum column J as well?

**ricoandquesa** · 10-24-2007, 11:17 PM

Hi Paul

How could I adjust the formula to find local and locals and add them together?

Thank you Quesa

**starguy** · 10-24-2007, 11:52 PM

Originally Posted by ricoandquesa

Hi Paul

How could I adjust the formula to find local and locals and add them together?

Thank you Quesa

To SUM

=SUMIF(E:E,"local",J:J)+SUMIF(E:E,"locals",J:J)

**ricoandquesa** · 10-25-2007, 12:59 AM

Originally Posted by pjoaquin

Hi again,

So are you trying to just count the number of times 'Local' appears in column E? Or are you wanting to sum column J as well?

Heya

I was trying to both - one to coun the times local appeared and the other one to sum column J.

I think I worked it out how to do the count - =COUNTIF(E:E,"Local")
That gives me all the "local" in column E!

What do you think?

**ricoandquesa** · 10-25-2007, 01:00 AM

Originally Posted by starguy

To SUM

=SUMIF(E:E,"local",J:J)+SUMIF(E:E,"locals",J:J)

Hi there,

Thanks for that - I had worked out one formula as well -

=SUMIF(E:E,"local",J:J)+SUM(SUMIF(E:E,"locals",J:J))

but I think after seeing yours mine is unessarily long + pointless!

Thanks for the help

**shg** · 10-25-2007, 01:06 AM

Or just

=SUM(E:E = {"local","locals"}, J:J)

**ricoandquesa** · 10-25-2007, 03:16 AM

Originally Posted by shg

Or just

=SUM(E:E = {"local","locals"}, J:J)

Even better - thanks

**ricoandquesa** · 10-25-2007, 03:36 AM

Nah sorry that didn't work that just added up everything in that column - I want to only add up corresponding cell that has local and locals

**starguy** · 10-25-2007, 04:36 AM

Originally Posted by ricoandquesa

Nah sorry that didn't work that just added up everything in that column - I want to only add up corresponding cell that has local and locals

Please attach sample file in zip format.

**owainl** · 10-25-2007, 10:13 AM

Originally Posted by shg

Or just

=SUM(E:E = {"local","locals"}, J:J)

I think you mean

=SUMIF(E:E = {"local","locals"}, J:J)

**ricoandquesa** · 10-25-2007, 10:41 PM

Originally Posted by owainl

I think you mean

=SUMIF(E:E = {"local","locals"}, J:J)

I tried this as well it didn't work

Currently I have the formula
=SUMIF('6'!E:E,"local",'6'!J:J)+SUMIF('6'!E:E,"locals",'6'!J:J)
the answer that i got is 5.40

I just tried
=SUMIF('6'!E:E={"local","locals"},'6'!J:J)
and that came up as a formula error

Then I tried
=SUMIF('6'!E:E,{"Call to 13 Number","Calls to 13 Numbers"},'6'!J:J)
the answer was 3.00 and that is wrong

Any ideas....

**Paul** · 10-25-2007, 11:12 PM

Seeing as we're 14 posts, wait, now 15, into this thread, I think it's going to be necessary for you to upload a copy of your sheet, or an example of it, and note what results you're expecting.

I'm still not even sure if you're trying to sum or count cells, to be honest.

To upload an excel workbook you must first zip it, then when replying click the paperclip icon, browse for your zip file, upload it and then close the attachment window.

To zip the file you can use a zipping utility such as WinZip, or if you have Windows XP you can simply right-click on the file and choose Send To -> Compressed Folder. That will create a zip file in the same folder as your excel file.

**ricoandquesa** · 10-28-2007, 08:08 PM

Hey there,

I have shaded the cells that we are working on. The formula that is there now works fine. I need to have both count and sum. There where some other suggestions that the formula could be shortened but they didn't work very well.

Thanks Quesa

**Paul** · 10-28-2007, 08:52 PM

I think I know what you're asking for.. to shorten the SUMIF and COUNTIF formulas?

If so, these seemed to work:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Since '13 Number' is the same between both instances, this works. I suppose you could use just *13* instead, but that might pick up any other instance where 13 is anywhere in the cell (if that even exists?)