sumproduct countifs

  1. S

    Multiple Countifs using sumproduct

    Hi All, I have a workbook where I need one countif formula to incroporate multiple cells for one section. I have used adding the countifs together but it makes me workbook run super slowly. I know there is a way of using some products for the same thing but dont know how to actually do it...
  2. D

    SUMPRODUCT - Summary Sheet Drop-Down Menus

    Hi there, I have two sheets on a workbook - a summary table and a master record table. For each record on my master table, I want to add it to a count on my summary sheet (see below). <tbody> Summary Table Jan Feb Mar Apr May Name 1 0 0 0 0 0 Name 2 0 0 0 0 0 Name 3 1 1 0 0 0 Name 4...
  3. S

    Sum(Countifs( ... use cell reference instead of Text in constant array .....

    I have some text in cells A3:A6. The text is: Testing1 Testing2 Testing3 I have a Sum(Countifs( formula in cell B7. The formula is: =Sum(Countifs(CriteriaRange1,{"Testing1","Testing2","Testing3"},CriteriaRange2, Criteria2, etc. etc.)) I would like to reference the cells in my array instead...
  4. G

    countifs or sumproduct help

    Hi, I have some data like below. Month Customer MIKE JANE 1 ABC123 PLT123 ABC123 2 ZSW123 ZSW123 MKL123 3 MKL123 3 PLT123 5 MKL123 6 PLT123 6 ABC123 6 ZSW123 6 ABC123...
  5. S

    Help please! - Count Unique with 2 criteria

    Help! Excel Gurus, I've been trying to figure this one out for the past few hours, but am completely stumped. Below is my sample data. TransactionID Date Location 201611609 2016/4/24 A 201611609 2016/4/24 A 201611609 2016/4/24 A 201611611 2016/4/24 B 201611611 2016/4/24 B 201611612 2016/4/24...
  6. P

    Count once multiple CountIF criteria...

    Hi All, Apologies if this is a repeat of another question, couldn't find one which quite matched... I'm afraid I've reached the point where I must ask for assistance, I'm trying to count the number of rows in a given dataset which match a set of criteria, but specifically count the row only...
  7. D

    Count number of rows where two columns values are matching and third column is null.

    Hi All, I have a very strange condition where I have to get my answer in a single cell. Note: I do not want to populate any randome cell and refer that in my cell function. See the below table: COLA COLB COLC COLD 123 123 Y 223...
  8. F

    Uniq values

    Hi, I have function to count how many many times name from column C (sheet M1) appear in range (sheet CSTG, column B): =COUNTIFS(CSTG!$B:$B,'M1'!C7) <tbody> Column C Column D - only count Audi 5 BMW 7 VW 10 Fiat 3 </tbody> Now in column Q (sheet CSTG) I have different values, like...
  9. I

    Sum total hours "when in operation" between 9:00pm and 8:00 am

    Basically, I am able to calculate the total hours in operations. Now, I need to sum the hours in Operations that fall between 9:00pm and 8:00 am. <colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181; mso-outline-level: 1;"> <col width="87" style="width...
  10. E

    Counting number of rows to multiple criteria when the sum of a row range is greater than 0.

    Hello, Thank you for taking time to read my query/challenge?? I have been having a mental blank for the last two evenings on this and decided to ask you wise people for help. The question summary is at the end. Sorry it is long, I have just tried to add as much detail as possible. I have the...
  11. F

    SUM per City

    hi, I have simply formula SUM: =SUMPRODUCT('Sheet3'!$BF$2:$BF$100*'Sheet3'!$AX$2:$AX$100*(Specific_Name=A10)) In coulmn BB I have name of the CITY - Tokyo, Paris, Londyn etc... Now I want to SUM exactly this same but depending on the selected city (example: 'Sheet1'!A1= 'Paris') Could you...
  12. T

    Return excact data if between 2 time stamps

    Hi. I'm not sure of what formula will get the desired results, but here is what I'm trying to accomplish. I have a data table where column A lists "names", column B lists "items purchased" and column C lists "time purchased". I am trying to use a formula to show all information where items were...
  13. S


    Can anyone please help me with this. I would like to find out what is the profit / day. Every time i buy my QTY and Cost is different. Every day my sold QTY and Sold Price is different. It needs to use old inventory with old cost until that is gone then use the new inventory with new price. For...
  14. J

    Multi Condition SUMPRODUCT

    I’m having difficulty with a SUMPRODUCT formula. I have a list of transactions on one worksheet (Data) that have a date and a time in each row. On my second worksheet I’m trying to do a count of each transaction that falls on a specific day and within a defined time period. I want to be able...
  15. K

    Getting my head around SUMPRODUCT

    Below is an example of a sheet in which I collect data on the quality of the work each employee delivers. From column H onwards I register if an employee did the CPI (Critical Process Indicator) correctly or not. One mistake and the formula in column D goes to 'no', but if all the indicators in...
  16. V

    Using Sumproduct instead of Sumif / Countif

    Hi everyone, Sorry but I can't get my head around this - I have created a workbook with a number of sumif & countif functions. However, I didn't realize that they don't work if the sheet that they are refering to are closed! I can't keep all the files open as there is way too much info. I...
  17. A

    Countif staff name if date in range

    Hi all, on a staff roster I would like to get a count of the shifts per individual for the pay period. Eg. If the pay period is 1/6/10 to 10/6/10 how many shifts is Allira working? B & C are filled down from a master date & then formated "ddd" & "d". ie B1=A1, B2=B1+1, B3=B2+1 etc. Same for...
  18. T

    Sumproduct help

    Morning All firstly i apologise if this is in another thread ive searched but cannot get any to work. i'll try and make it as simple as i can i have 2 columns USER ID (A:A) and DATE (B:B) and 2 date cells(countaing a start and end date- call it C1 and C2 which can be changed by the user)what...
  19. T

    Formula to count occurances in a given date range

    I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but...
  20. S

    I know I can't use COUNTIF with multiple conditions

    I am trying to get a count of the number of rows in a worksheet that meet the following conditions: 1. Date in a specified cell is greater than today's date - ie: (BD$35<TODAY())<TODAY()< font> 2. A specifed range of rows contains the word/characters TBD - ie: =--ISANUMBER(SEARCH(“TBD”...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back