# SUMIF And VLOOKUP

1. ## SUMIF And VLOOKUP

Hi,

I set up a SUMIF forumula shown below;

Please note that the formula is set up in "Data Table 2":

"=SUMIF('Data Table 1'!E5:E100,A3,'Data Table 1'!G5:G100)",

where within column E - cell 5 to 100, the criteria in cell A3 (in Data Table 2) is searched for and then adds all the numberical values with in column G - cell 5 - 100 (in Data Table 1) that correspond to the criteria in cell A3.

This formula works well for what I needed, but I need somthing a little different for my next project.

I need to search for a criteria in a column A, once I have found all the cells (there may be more than one cell that meets the criteria) that meet the criteria, I need to find in column B all the cells that have the word "Incomplete" associated within the row that meets the criteria. So...is there a way I can combine SUMIF and VLOOKUP formulas to do this.

Thanks in advance for any help

Calli  Register To Reply

2. Try sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Something like

=SUMPRODUCT((A1:A2000="Criteria1")*(B1:B2000="Incomplete"))

VBA Noob  Register To Reply

3. Hi,

In the formula you gave me, can the "Criteria 1" be a reference cell, so the formula would like the following:

=SUMPRODUCT((A1:A2000="A3")*(B1:B2000="Incomplete"))

Is this possible?  Register To Reply

4. Yes you can do it that way but you don't need the quotes around the reference cell (otherwise "A3" will be interpreted as the literal text "A3" rather than the contents of that cell) so use:

=SUMPRODUCT((A1:A2000=A3)*(B1:B2000="Incomplete"))  Register To Reply

5. Hey,

thanks alot it worked, but still have one more question. In my data table, I have the words "Incomplete" followed by some commentary (details of the issue). Is there away to incorporate the "FIND" formula in to the equation so that its just looking for the word "Incomplete" despite what text follows it.

Calli  Register To Reply

6. Try

=SUMPRODUCT((A1:A2000=A3)*(ISNUMBER(SEARCH("Incomplete",B1:B2000))))

VBA Noob  Register To Reply

7. thanks it worked  Register To Reply

8. Glad you found a solution

VBA Noob  Register To Reply

9. ## Re: SUMIF And VLOOKUP

I have a similar problem. I want a value to be associated with A, B, and C. Then I want to type either A, B, or C into a cell, and have Excel spit out the sum of the values mentioned in that sell... I searched sumif vloockup, but I'm not sure this will do it or not...  Register To Reply

10. ## Re: SUMIF And VLOOKUP

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.  Register To Reply

11. ## Re: SUMIF And VLOOKUP

Hello,

Let's say, that in row A1:A10 you have numbers from 1 to 10
In Cell B1, you calculate average of the Range A1:A10
In Cell C1, you would like to SUM all the numbers from Range A1:A10 (this is a evaluation and sum range, to be more exact) that are greater then average value in B1!
Formula =sumif(A1:A10;">B1";A1:A10) is not working, since Excel "seas" the formula in Criteria field with "" meaning, that Criteria searches B1 or even >B1 as a string in A1:A10 range.
If Instead of B1, I enter a value, which is a result displayed in B1 Cell, than it works fine (eg: =sumif(A1:A10;">4,52";A1:A10)) but this is not a solution, since the value in B1 could be dynamic and I don't want to change sumif formula every time, value in Cell B1 in changed.

BR  Register To Reply

12. ## Re: SUMIF And VLOOKUP Originally Posted by daddylonglegs Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.  Register To Reply

13. ## Re: SUMIF And VLOOKUP

Thanks, got it.  Register To Reply

14. ## Re: SUMIF And VLOOKUP

thanks it worked  Register To Reply

15. ## Re: SUMIF And VLOOKUP

I am not sure if this the correct formula set:

I want A3 to look up [search] A20:A30 and if it matches [in this case a name] then put into B3 the corresponding value in B20:B30 that is next to the name.  Register To Reply