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

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?

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

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

thanks it worked

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

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.

Hello,

Thanks for your help in advance!

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

Thanks, got it.

thanks it worked

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.