Simple VLOOKUP assistance

[andrew1551]

HI, I'm sure this has been addressed before but despite my efforts I have only made it so far.

I'm attempting to do a simple VLOOKUP function, I have a excel copy of my pilot log book and all I want to do is check 1 column for a type of Aircraft ie. C172 then add the value is another columb ie. command.

{=SUM(VLOOKUP(C12,C:S,13,FALSE))}

The above works but only returns the value for the first row where C172 is found then stops, I wish to continue through the whole column and keep adding.
Any help would be appreciated thanks.

This screenshot might help
Capture.PNG

[joeu2004]

I think you want:

=SUMIF($C$1:$C$2000,"C172",$S$1:$S$2000)
or
=SUMIFS($S$1:$S$2000,$C$1:$C$2000,"C172")

I prefer the second formula because the order of the parameters make more sense, IMHO, even though you have only one condition (so far).

Note some changes:

1. Note that the string "C172" must be quoted. C172 without quotes is a cell reference, that might contain the search string. It is unclear which you meant because the image does not show cell C172; however, it does show rows with the string "C172".

In the future, attach an example Excel file, not an image. See the yellow-highlighted banner at the top of this forum's webpage. Hard to miss, I think.

2. The formula is normally-entered (just press Enter as usual), not array-entered as you did.

3. I chose to sum column S because that seems to have a lot of numbers to add. But in your VLOOKUP formula, "C:S,13" would select column O, the 13th column to the right, counting column C as relative column 1.

If you want to sum some other column(s), please identify them by name, so there is no ambiguity.

4. I changed whole-column references C:S to a limited range like C1:C2000. Change 2000 to the largest row number that you are ever reasonably likely to use. I'm sure it is not 1+ million column, which a whole-column range might search. (Arguably, SUMIF and SUMIFS might be smart enough to only search up to the last row used in the worksheet or column. We don't know.)

On second thought, based on the image, I would suggest $C$1026:$C$1048 and $S$1026:$S$1048. That woud automatically adjust if you use Insert Row to add data. And you are less likely to add numbers from irrelevant rows that have "C172" in column C by coincidence.

[andrew1551]

Thanks bud it worked a treat. Just so you know the C172 was not a cell reference it is the aircraft type (Cessna 172). Sorry I wasnt more clear but your instructions were and easy to work with. Thanks again.