1. E

    Simplifying a formula for the Normalised Gibbs Entropy

    Hi, I want to write a formula that computes the normalised Gibbs entropy, which is computed as follows: The values that I want to test this for are located in column Z, AD, AH, AL, AP, AT, AX, ..... CL etc. so 3 columns are skipped every time. I want to test for values 0 - 10. I already...
  2. M

    Formula to Count Certain Leads for People Within a Specific Month for Sheets

    My Google sheet can be referenced here. On the 2021 Openers tab, I need to create a formula that will allow me to count the total amount of leads associated with a specific person within a specific month, referencing the data on the 2021 Lead Data tab. Formula criteria to consider: The Leads...
  3. J

    CountIfs Time Stamps

    Hi all, Having trouble with a CountIfs function. I am trying to answer the question: At any given time (down to the second), how many people were in attendance at our online course? My spreadsheet looks like this: A B C D E F SessionStart SessionEnd Concurrent Viewers 7:00:00 PM...
  4. M

    Countifs combined with multiple index/match

    I just can’t crack this one. Can anyone give a hint in the right direction, please? I have this one sheet (Daily presens) where all of the leaders for each departement enters if the staff is pressent. Then I have another sheet (qualifications) that contains all the certifications each worker...
  5. Y

    Count a select total of one column using two criterias

    Hi, I need a formula to calculate a total number of incidents that are 1) active status and 2) raised by the target users. I have shown a small example in below image. Thank you in advance Regards,
  6. T

    Multi Column Data Validation in Excel Table

    Hi Everyone, I have an Excel table. I am trying to perform a multi-column data validation. I am trying to use the COUNTIFS function like so: =COUNTIFS([Supplier],C2,[Supplier_Code],D2)<=1 The goal is to ensure that the data entered is not a duplicate. The combination of [Supplier] and...
  7. P

    Formula to count unique values based on multiple criteria, including 1 column where at least 3 of 5 criteria should be found?

    Hello everyone, I'm not sure what kind of function of is needed here, though here's a sample of the data and what I'm trying to do: Session 1 Startup Arabic Yes Turkey Male Syrian Session 2 Startup Arabic Yes Turkey Male Syrian Session 4...
  8. I

    Multiple Countifs - I think

    I have searched, tired, failed. I am trying to count across multiple cells. For example, A is a name and D is a "problem". I want to see how often a person has that problem and countifs worked great. Now the problems are spread across C:H and it won't work. Is there a better function? In this...
  9. A

    Counting if issues

    Hi everyone, I am having issues counting the number of Males/Females that were enrolled in a study during the month of March (3/1/20-3/31/20). I'm not sure why, but when I try to use the COUNTIFS formula to figure out the number of females that were enrolled in March (between 3/1/20 and...
  10. H

    CountIFs with comparison of variable extracted value (LEFT Function)

    Hi, I am new here and have run into a problem with Excel. I have the following values in a column. I want to first extract the numerical value present before the space (i.e. 7.5 in first cell) in each cell then show me the count of the cells having that value greater than say "6". So that in...
  11. A

    Countifs with dynamically altering criteria

    Hi Guys, I am trying to create a formula where I can draw data from a dataset which has 3 fixed ranges, 2 fixed criterion and 1 dynamic criteria. As I have shown in the uploaded images, there are the months, types of items and customers. All this data is drawn by a VBA code to the raw data...
  12. A

    Countifs based on text and Color

    Hello, I am needing a formula to perform a countifs if Column A contains "Andrew" and Column B has the interior.colorindex =35 I have been using the below function which I think is quite commonly found on the web, and works fine but only counts column B and has no reference to column A or any...
  13. D

    Question about formulation of some desired output with CountIFS?

    Hi Everyone, I need to formulate following two desired output whose explanations can be also found in EXCEL I have copied here. I though I can use only countifs to formulate them but I could not get it worked. I will be glad with your help. Thanks you in advance...
  14. G

    CountIFs number of entries which match two different variables between two dates

    Hi all, I am looking to be able to count the number of entries between two dates which match two different variables. In the example attached, I want a formula which will count the entry if it contains the relevant product AND Name of sales person. My code at the moment is...
  15. W

    Countifs with multiple criterias

    I'm trying to get a total count of how many times a tag was used, but the table export I have isn't the best to work with. I'm not able to just add in two criterias for countifs like I typically do. I'm thinking I would need to do a countif+index match, any one can find the easiest way to do...
  16. Wad Mabbit

    SUMPRODUCT & SUBTOTAL with same criteria ("S") across two non-contiguous columns [D3 Activity] & [D4 Activity] to return count of [Name] in either

    Hi, I have: SUMPRODUCT((tblStudentProgress[D3 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D3 Activity])-MIN(ROW(tblStudentProgress[D3 Activity])),0)))) + SUMPRODUCT((tblStudentProgress[D4 Activity]=Satisfactory)*(SUBTOTAL(103,OFFSET(AW3,ROW(tblStudentProgress[D4...
  17. A

    COUNTIFS - Too Many Arguments

    I thought I understood the formula but I keep getting this error. Goal: 1. Count the number of "Admission" in Column B, only if Column J says "Vent" 2. Count the number of "Discharge" in column B, only if column J says "Vent" current formula: =COUNTIFS(B5:B28,"ADMISSION", J5:J28,"VENT") I...
  18. T

    Rank duplicate values with COUNTIFS

    Hi experts, I have a data set looking at 3 types of fruit, their order dates, and order cost. I want to rank the line items by earliest date. However, since some orders have the same order date, I get duplicate ranks (see two rows of Apples ranked as #4 in the attached image). I am using a...
  19. D

    How can I write the COUNTIFS function only one time when it comes to different conditions?

    Hi Everyone, I put here the table for your perception. Week 1-2-3-4 => January Week 4-5-6 => Febraury I am here calculating total job concept type numbers at each month. How I am solving it currently is a bit time-consuming. Therefore I want to improve that formula that I am using. What I...
  20. N

    Using Countifs to Calculate Headcount

    Hi Team, In my attached screenshot I am wondering how I can use COUNTIFS to calculate the current Headcount of employees based on the following conditions. Countif; 1. The employee class is NOT "Adecco" nor Blank. 2. The Contract Startdate is less than TODAY(todays date) and not blank. 3. The...

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