# sumif and countif

1. ### Sumifs+index formula help!!

Hello , NEw to this forum and would to get help(first thank you in advanced)!! I need help to create a formula that sum the following Summary below? <colgroup><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody> </tbody>
2. ### Counting unique value with a date range

I have a sheet with 60k+ lines of date for orders and I am wanting to pull the number of orders with a given date range based on a class of order. For this one I will just use all web orders as what I am looking at. There are several columns of date but the relevant ones are Col B - date the...
3. ### Help with value error and external spreadsheet

I need help changing my countif to a sumif formula so that results are available when the worksheet is closed. My countif formula is simple and works fine when the sheet is open. =COUNTIF('[DISTRICT G.xlsx]DIST G ALL'!$N:$N,">0") I have tried: =SUM(IF('H:\Cm\pi\ps\ddr\[DISTRICT G.xlsx]DIST...
4. ### Count / Sum data from two different sheets

I have two worksheets Worksheet "ORDER" <tbody> A B C D E 1 Part Name Order 1 Order 2 Order 3 Order 4 2 Nut Bolts 1 1 3 Pistons 2 2 4 Hammers 1 2 </tbody> Worksheet "PRODUCTION" <tbody> A B C D 1 Part Name Stock Order To be produced 2 Pistons 1 3 Hammers 2...
5. ### Sum only the first instance of a number tied to an ID, and then only sum those that tie to a label criteria

Hello and thanks to anyone who can help! I am trying to: In cell B2, Look at column A7:A23, and only sum numbers in D7:D23 if they match what is in A2 But, I *only want the first instance* of the numerical value per project ID in D7:D23, because otherwise, I double count. For example...
6. ### Sum Multiple Columns with Multiple Criteria

Say I have the following chart (See below) and I'm trying to sum the regions that have product overlap. What type of SumIf or SumProduct formula could I use to accomplish this task? <tbody> Product # Region 1 Region 2 Region 3 Region 4 Region 5 Region 6 Region 7 Region 8 Region 9 1...
7. ### Countifs working with SUM/SUMIF

So, here is my conundrum... I am trying to summarize data by month, based on a couple of variables. Example: If the Value in a column is YES, and a value in another column is a specific month, then summarize the total dollars in a third column. Now, if I was only looking for the count of...
8. ### Vlookup needed?

Hi I need to summarise each of our suppliers invoices on a separate forecast/actuals tab. Can I use VLOOKUP for this? Example month .... <colgroup><col><col><col><col span="2"><col><col span="2"><col><col></colgroup><tbody> Date Type Description Total NET VAT Expected Income...
9. ### =SUMIF and =COUNTIF in the same formula

I am looking to create a formula using both the sumf and countif operators. Example, Column A contains store numbers. Column D contains deposit variances(positive and negative numbers). However, there is an entry for every store, every day, even if there is no variance So, what I am try to...
10. ### Sum values in one column until values in another column reach a certain value

I need to get the sum of the prices when the contract is ZS but need the sum per quantity of 10. So in this example, i need to find the sum of first 4 columns because that will make the quantity add up to 10. The problem is, i copy and paste in sets of data where the "ZS" contract will fill at a...
11. ### SUMIF using multiple worksheets

I have a statistics spreadsheet I've create that has 3 tabs--One a summary, and the other two running totals of reference statistics. I'm using a SumIf formulate to calculate the total time spent on each specific date from the running totals sheets. That amount then goes on to the summary sheet...
12. ### Is the sumif the best option?

Good Morning MrExcel! I am trying to get the syntax correct but I am not 100% sure the SumIF is the right command for what I am after or if it should be some very long if statement. I would greatly appreciate any insight you have: I think can do this with pivot charts to get the sum but I am...
13. ### SumIf function/VBA help

Hey Mr.Excel I currently working on a project there i need find the total effort of assignments. The effort is in a Excel ark. This is an example on the ark. <tbody> A B C D E F G ID Item Release PlanID OP Prio Effort X Product 20-1 10 Mads 1 10 X Product 20-1 10 Mads 1 5 X Feature...
14. ### Count workers hours: I think this is easy. I am just not seeing it!

Hi there, I created a workbook that I use as a schedule for all workers in my department. The department breaks out into 4 other sub departments. All employees are scheduled for either a 4 or 8 hour shift. I need to quantify the data in a number of ways, most of which I was able to write on...
15. ### VBA SUMIF with multiple criteria

Good Morning! I have written a sumif sub that summarizes "value" based off of the criteria in the three columns to the left. <tbody> Year Contract type Price Range Value Year1 A Twenty > 19 Year2 B Fourty + 45 Year2 C Twenty > 15 Year2 D Fourty + 50 </tbody><colgroup><col><col...
16. ### Count If Month is Current Month

Here is the Issue I have 3 Pie Charts, they are populated from a spreadsheet that has a Status page (pie charts are located there), and the status page is updated from 3 additional sheets from the spreadsheet. I need the pie charts to only update with the current month data only, the sums of...
17. ### Question on SUMIF vs SUMPRODUCT and how to return a total in one column based on values in other columns

I have a massive excel spreadsheet of data that contains thousands of URLS and web performance data. My table structure is: Col B = Microsite Col I = URLs Col S = Total Page Views I am trying get extract the total in range S3:S1048576 where the values in range B3:B1048576 = "xyz", and the...
18. ### COUNTIF function for filtered tables

Dear Madame /Sir I am using an excel sheet where I am applying auto filters using VBA. In Column 6 , I have type of training (eLearning, InClass, Webex) and in column 7, I have time required for the training. After applying filter I am able to see how many hours of training required for...
19. ### Counting unique text values based on month

I'm exporting data from Salesforce monthly in order to summarise our sales pipeline. The data come out as: <tbody> Account Name (A) Contract end date (C) Company A 20/05/2016 Company D 23/11/2015 Company C 17/09/2015 Company A 31/01/2018 Company B 31/01/2018 Company B 03/05/2018...
20. ### Counting Unique Values If Value in Another Column is True

I'm trying to find the sum of unique values in column A but only if the corresponding value in column C is 'true'. Right now I can find the number of unique values in column A but can't seem to figure out how to make it only count certain cells. I have...

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...