I have searched the forums to see if there is a similar prob already answered but to no avail (apologies if this has already been answered).
I am designing a sheet for a Uni project that needs to calculate the average attendance rates for students between age groups. My data has the age in a specific age for example:
Age
18
19
19
21
etc.
I am trying a SUMIF function that adds all attendance rates based on a criteria - however I can't get the criteria to fall between two ages ranges (more specifically, I need to sum the averages where the ages is between 21 - 25), as the function seems to only allow for one expression of criteria.
My function currently Reads:
=SUMIF(A1:A25, ">=21 & <=25", B1:B25)
Where A is the column recording ages and B is the column recording attendance rates. It presently returns 0.
Is there a better function for this task, or a way around the SUMIF criteria?
I am trying to find a formula to find out peoples ages from their date of birth but on different dates.I know you can do age from DOB 'today' or on a given date, but I have date of birth in column A and the date they were seen in column B and I would like their age in column C. Is this possible?
eg
Date of birth | Date seen | Age at date seen 01/05/1989 | 01/01/2011| ? 02/02/1956 | 02/03/2012| ? 05/02/1930 | 05/05/2009| ?
I am finally at the end of this project. I need to find a code that will copy any lines that the person has turned 65 in the previous month or that will turn 65 in the current month to the Monthly tab.
I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5% Like having filter criter = equal or greater then 1 hour, if a1 > 5% Then criteria + 1 hour If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:
[Code] ......
I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?
Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
I am trying to find the top ten students house points from each of five different houses.
I have no problem finding the top ten highest points value from each house, but I also need to return the name of each corresponding student in a seperate cell
I have played with some formulas (mix and match method) but the problem is when the same value for house points appears for different students in different houses.
In the attached example (the actual sheet is much bigger) the second return 225, gives the wrong name. I want to have a series of top ten result tables for each house.
Thought I’d cracked it (F6), but now hit a brick wall. Are IF statements the wrong way to go?
I am trying to solve a problem related to 3 criteria of selling food item. Please see attached file to get my problem and what I am looking for. for example i provided for two days data. I have to do this for seven days. Pls let me know if u hvae any question.
In workbook B, I have a bunch of product / revenue information related to each customer. Each customer may have multiple rows of data on this workbook, but each row would be a unique Cust & Prod ID combination: ...
if it is possible to choose several criteria in this formula, for example: SUMIF('Global Sheet'!F5:F5502,"<"&'Research per date'!E20,'Global Sheet'!K5:K5502)
I would like to add a new criteria after "<"&'Research per date'!E20
I want to calculate the sum of a range of cells but when a cell contains a specific figure (or trigger if you like), the sum starts again from the next cell.
So, A1 to A11 can be used for the simple sum but A12 contains data which is not part of the sum and I want A13 to A20 to be a new sum starting from the cell after the trigger was found (A12).
