# 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

2. Try sumproduct

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

Something like

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

VBA Noob

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?

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"))

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

6. Try

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

VBA Noob

7. thanks it worked

8. Glad you found a solution

VBA Noob

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...

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.

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

12. ## 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.

13. ## Re: SUMIF And VLOOKUP

Thanks, got it.

14. ## Re: SUMIF And VLOOKUP

thanks it worked

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.

16. ## Re: SUMIF And VLOOKUP

dmbedge, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1