I'm trying to find total gross commission within a spreadsheet, where gross commission found in Deals column Q. Columns T & U indicate with an "X" whether the deal was associated with referrals and such.
I have written this formula below. I do not know the correct part of this formula that will add the numbers I have in Column AB2:AB552 (bold below). As it is, this formula is counting the number of cells in that range that has numbers in it, but I need it to total those numbers as my final result.
This is the function I have now: =COUNTIFS(Sheet1!A:A,Sheet2!A2,(Sheet1!E:E),"*") and it works great, counting every cell with a value in column E.
When I make criteria range 3 another column (criteria also being"*") my numbers actually start going down. I think it might be averaging the two columns out or something but I just want it to count all the cells with values in it from multiple columns and give me a total count.
I have 4 columns total that I want to be able to get a total count from.
I do hav a sheet in which i do hav some column;s n data
Worktype Metrics April May June
in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.
I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?
I have a userform with a bunch of textboxes that read from certain rows/columns/etc. However, when I hide certain columns (I need them hidden) the textboxes don't display the data (it only displays when the columns are not hidden).
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming - Converting to a different file format (didn't work, it won't let me) - Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I am using the formula to count if c- is in the cells: =COUNTIF($B2:$U2,"C-")
However, I need to change the formula so rather than just count if C- is in the cells, I need it to count if A*, A+,A, A-, or B+ are in the cell instead.
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
I'm trying to do a COUNTIFS for a set of data. What I want it to do is pull everything that is assigned to a certain person that has no date in one column, and information in another. For example:
Name Date Comment
Michelle 4/30/14 Closed
Michelle
Issue
Michelle 4/30/14 Closed
Michelle 4/30/14 Closed Michelle Issue
So based on this dataset, I would get the number 2 for Michelle (row 2 and 5 have no date in column B and a comment in column C). I've tried a few variations and can't seem to make it work right.
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,
If column A=a AND column B=1, add column B on the same axis. I can only get a function that adds all B column if A=a. The function should equal 3. Cells B1, B2 and B6 should be the only ones counted.
I have the following formula: =IF((COUNTIFS(data_sheet!U:U,Monthly_Report!H10))>166,0,COUNTIFS(data_sheet!U:U,Monthly_Report!H10,data_sheet!BN:BN,"Premier"))
This formula counts values on my data sheet based on the specified criteria. I have this broken ito 3 section, each section that needs to be summed.
Section 1 can not be greater than 165, if it is all the values in the range needs to be reset to 0 Section 2 can not be greater than 190, but at the same time can not be less than 166, else is need to be reset to 0. How would I adjust the above mentioned formula to check these criteria?
I'm currently using the following function: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000") which works perfectly. However I'd like to add another condition "between 1000 and 2000".
I've tried this but I'm not getting any luck: =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],AND("<1000",">2000")).
However, I want the formula to count values when ABS($AM:$AM)=ABS($AM15) but I get an error if I try to put ABS($AM:$AM) as my range. Is there anyway to set my range as the absolute value of Column?
I have a COUNTIFS function that, among other criteria, only counts cells in which the value is "P". I now want to modify this function to count cells in which the value is "LA" in addition to cells in which the value is "P". I'm not sure how to work this into the argument. I tried using OR("P","LA"), but that's not working.
I am having a problem getting my formula to return a result. The basics are that I would like a count of a specific set of data in a section of a workbook. So in column 'A' I am searching for 'TextA' but this also has to have 'TextB' in column C. The problem is I have 12 consecutive monthly worksheets I would like to pull this data from and I only really want to write one formula. I can (and started to) use the countifs function plus countifs function etc referencing each worksheet individually but this seems long winded.
I have read on this forum that I can use sumproduct combined with countifs but I can't seem to get it right. I have listed my formula for a single sheet countifs function below, but I don't know how or if to include the sumproduct function to reference the rest of the monthly worksheets. The other thing I should mention is that there are other worksheets too, so it would need to be specific to this range, but they are consecutive within the workbook.
I need to look at a series of data once per week and calculate the results. I'm trying to use COUNTIFS to get a result based on both the date I'm doing the analysis for and for the response.
I've attached a sheet that shows a sample of what I'm trying to do. Cell B1 contains the date range I'm using, and cell B18 shows the formula I'm having trouble with. I suspect it's because I'm trying to treat a date like text, but I'm not sure.
