Combining Vlookup Hlookup And Sumif (or With Array Sum(if... 

Combining Vlookup Hlookup And Sumif (or With Array Sum(if...  Excel 
View Answers 
I was reading the comments from the below topic and was very close to answering the question I had... but I need it to go a step further.
/ />
I have a table with the following layout:
1) Column A has an ID # (with same ID appearing on different rows)
2) Row one is the heading with a letter and can repeat across multiple columns.
I want to create a sumif using a vlookup and a hlookup, but I don't know how to combine the two and get the formula to work. For example in the list below, I want to sum everything that has ID6 and the columns with a B header.... I know this can be done using pivot tables, but I am trying to avoid this.........Please help! thanks
ID# A A B C A B
ID1 8 5 1 1 4 9
ID2 9 0 2 5 9 9
ID6 9 1 2 8 2 8
ID4 2 7 9 0 8 0
ID5 4 7 5 5 6 9
ID6 1 9 2 7 7 8
ID7 4 4 8 5 5 0
ID8 5 0 6 9 0 2
ID6 8 1 3 9 4 0
ID2 6 5 9 5 4 4
ID1 8 8 1 1 5 5
ID6 6 1 7 8 1 9
ID6 1 8 1 0 3 4
ID2 3 0 6 7 7 6
ID1 9 1 5 4 1 0
ID2 9 9 2 2 1 2
ID1 8 9 4 5 6 7
ID2 9 0 7 1 4 2
ID6 6 9 0 9 1 6
ID2 7 8 3 8 3 2
/ />
I have a table with the following layout:
1) Column A has an ID # (with same ID appearing on different rows)
2) Row one is the heading with a letter and can repeat across multiple columns.
I want to create a sumif using a vlookup and a hlookup, but I don't know how to combine the two and get the formula to work. For example in the list below, I want to sum everything that has ID6 and the columns with a B header.... I know this can be done using pivot tables, but I am trying to avoid this.........Please help! thanks
ID# A A B C A B
ID1 8 5 1 1 4 9
ID2 9 0 2 5 9 9
ID6 9 1 2 8 2 8
ID4 2 7 9 0 8 0
ID5 4 7 5 5 6 9
ID6 1 9 2 7 7 8
ID7 4 4 8 5 5 0
ID8 5 0 6 9 0 2
ID6 8 1 3 9 4 0
ID2 6 5 9 5 4 4
ID1 8 8 1 1 5 5
ID6 6 1 7 8 1 9
ID6 1 8 1 0 3 4
ID2 3 0 6 7 7 6
ID1 9 1 5 4 1 0
ID2 9 9 2 2 1 2
ID1 8 9 4 5 6 7
ID2 9 0 7 1 4 2
ID6 6 9 0 9 1 6
ID2 7 8 3 8 3 2
Free Excel Courses
Similar Excel Tutorials
Wildcards are characters that allow you to make more robust functions, searches, and filters in Excel. You put wil ...
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
Perform lookups on numbers with partial matches. For instance, find the first number that starts with 12 in a list ...
How to sum values that equal one of many potential criteria; this is basically summing with an OR condition. This ...
Subscribe for Weekly Tutorials
Helpful tutorials delivered to your email!
Helpful Excel Macros
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Similar Topics
I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request. Unfortunately, every time I do it I get the result #NA. I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result
Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?
Thanks
Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?
Thanks
Hi, can anyone help me with the below...
Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.
Lookup number 1 from column A, then give me the largest number from column B
example table:
A B

1 5
2 2
1 11
3 2
4 5
the result would be 11
thanks all!
Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.
Lookup number 1 from column A, then give me the largest number from column B
example table:
A B

1 5
2 2
1 11
3 2
4 5
the result would be 11
thanks all!
I am trying to write a formula that uses both an if statement and a vlookup statement.
It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.
So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")
But it doesn't work. Is it possible to nest a vlookup in an if statement?
It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.
So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")
But it doesn't work. Is it possible to nest a vlookup in an if statement?
I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
Hello Everybody,
I am hoping for some assistance. Here is what I have....
I am trying to get counts of certain values.
This is in the same workbook but different sheets.
On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.
An example of the formula is below...what am I missing?
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))
Thank you in advance for your assistance.
I am hoping for some assistance. Here is what I have....
I am trying to get counts of certain values.
This is in the same workbook but different sheets.
On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.
An example of the formula is below...what am I missing?
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))
Thank you in advance for your assistance.
Hi peeps
I want to combine data from several worksheets into one worksheet.
For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).
I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.
I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?
Any help will be beautiful.
Cheers
I want to combine data from several worksheets into one worksheet.
For example, I have data in Sheet1 (Columns A,B,C), data in Sheet2 (Columns A,B,C), data in Sheet3 (Columns A,B,C) all with varying amounts of rows. (All the rows contain text data).
I need to combine all of the data from the 3 sheets into a single sheet, Sheet4 (Columns A,B,C), eliminating the empty rows.
I've been looking into this for a while, and can't find anything that really helps. Anyone got any pointers of what to look into?
Any help will be beautiful.
Cheers
I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upperleft most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 45
rows.
Is there a way to copy each one  whether as a table or as text  into
a single Excel cell without losing data?
Many thanks.
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.
Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upperleft most data only.
What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.
The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 45
rows.
Is there a way to copy each one  whether as a table or as text  into
a single Excel cell without losing data?
Many thanks.
Hi,
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the lefthand most column. However, all the other columns are filled each with the grey dropdown filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter dropdown buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the lefthand most column. However, all the other columns are filled each with the grey dropdown filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter dropdown buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.
Sorry to ask such a bizarre question, but I appreciate your help.
Thanks
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
Hello,
I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?
I tried
/
but it does not work. Probably because column Q is filtered to show only blank cells,
Can somebody help me on this?
Thanks,
awagdarikar
I need vba code to Select first visible cell below the header row after applying autofilter on column Q of the data. Can somebody help me on this?
I tried
/
but it does not work. Probably because column Q is filtered to show only blank cells,
Can somebody help me on this?
Thanks,
awagdarikar
I have entered a vlookup formula in a cell and it returns the correct value from the other worksheet. However when I copy the formula down it keeps returning the exact same value as the first cell. If I enter a formula in each individual cell it returns the correct value. Its driving me crazy ! I woyld be glad of any advice
Many Thanks
Karamazov
Many Thanks
Karamazov
I have set a VLOOKUP formula to match account numbers. How ever I have to
click double click on the cell before the formula recognises the account
number. I have to do this for each cell. I have tried different formats. It
is not saving me that much time.
click double click on the cell before the formula recognises the account
number. I have to do this for each cell. I have tried different formats. It
is not saving me that much time.
Hi Guys
I have the following in G2:
=VLOOKUP(A2,Sheet1!A:D,4,FALSE)
The formula returns the correct result, which in this case is a number  2
When I fill down my range, the formula copies correctly, but every result is the same. 2.
However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.
Any ideas whats going wrong?
I have the following in G2:
=VLOOKUP(A2,Sheet1!A:D,4,FALSE)
The formula returns the correct result, which in this case is a number  2
When I fill down my range, the formula copies correctly, but every result is the same. 2.
However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.
Any ideas whats going wrong?
Hi,
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.
Please HELP!!!
Hey there,
I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.
Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.
Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.
I'll post up further comments as I am trying to work my way through it!
Thanks,
Jag
I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.
Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.
I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.
I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.
Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.
I'll post up further comments as I am trying to work my way through it!
Thanks,
Jag
Hi everyone....this is my first post here ....and not my last I believe!
My question I believe is easily solvable for you cracks of excel.
I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
I created an example on a worksheet attached just for you to understand.
I apreciate all the help I can get.
Thank you so much.
RG
My question I believe is easily solvable for you cracks of excel.
I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
I created an example on a worksheet attached just for you to understand.
I apreciate all the help I can get.
Thank you so much.
RG
I have created an excel spreadsheet to track attendance of an exercise class I am teaching. I would like to know each person's current percent attendance. (so if we have had 5 classes and they have attended 4 of those classes then it should show 80%). I would like it to keep a running tab, so as I add in the person's attendace it will continue that count, up to the 26 classes that are being offered. I thought if I did a sumif/countif I could get it to work, but it doesn't. Here is what I tried: =sumif($e$4:$ad:4,">0")/countif($e$4:$ad$4,">1"). Do you have a suggestion that would work? Thanks!
Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.
I am running Excel 2011 for Mac.
I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.
Appreciate any help, let me know if you have any further questions.
Hunter
I am running Excel 2011 for Mac.
I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.
Appreciate any help, let me know if you have any further questions.
Hunter
Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.
Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.
Any help anyone could provide would be WONDERFUL. Thanks!
Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.
Any help anyone could provide would be WONDERFUL. Thanks!
I'm attempting to filter columns E, G, and I all at the same time. I need to
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.
Does anybody know if it's even possible to do what I'm trying to do?
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.
Does anybody know if it's even possible to do what I'm trying to do?
Good afternoon,
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?
eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.
Other than creating a table and using a vlookup function, I am hoping there is a better way?
Darren
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?
eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.
Other than creating a table and using a vlookup function, I am hoping there is a better way?
Darren
In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white did not work
I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.
any Ideas???
Thanks
This is my first post in these forums.
I am trying to create a formula which will look at the data in columns A  I (50 rows) and copy this data to columns K  S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
I am trying to create a formula which will look at the data in columns A  I (50 rows) and copy this data to columns K  S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I am sure there is a relativley simple answer but it is driving me mad!
Hope you "Guru's" can help
Thanks
Added example spreadsheet to aid assistance.
Hi All,
If I drag a formula to the right it adapts the column letter in the formula from A to column B, which is standard Excel functionality.
However, if I drag the formula to the right I want Excel to skip a column each time.
In other words:
=A1 ; = B1 ; = C1
Should be
=A1 ; = C1 ; = E1 etc...
Is there an easy trick to do this?
Thanks,
Michiel
If I drag a formula to the right it adapts the column letter in the formula from A to column B, which is standard Excel functionality.
However, if I drag the formula to the right I want Excel to skip a column each time.
In other words:
=A1 ; = B1 ; = C1
Should be
=A1 ; = C1 ; = E1 etc...
Is there an easy trick to do this?
Thanks,
Michiel
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times. Is there an easy way to do this?
Thanks in advance.
Thanks in advance.