Sumproduct With OR Logic - Count Number Of Rows In Dataset

Jan 17, 2014

I'm trying to count the number of rows in a dataset where column A is something, B is something, C is something, D is not something, D is not something else, and column E > 0 OR F is something but I just can't get the result I want.

My actual dataset is a staggering 294,000 rows and 46 columns so here's a simplified version:

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

I am trying to get my head around sumproduct. So I had a go at trying to use the following formula to add data in odd columns.

=SUMPRODUCT(ISODD(COLUMN(A1:J1))*(A1:J1))

The formula below works but I don't understand whats wrong with the one above. Can anyone please explain how I would use isodd to achieve this? What am I doing wrong?

I'm sure that there had been an error somewhere but I can't figure it out. I'm getting zero value. Situation: need to count number of leaves within a specified period, thus I opted to use Sumproduct.

I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.

I have tried using this SUMPRODUCT formula: =SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones"))) but it is generating incorrect values!

Specifically: - Jones shoulld return 1 - Franks should return 3 - Smith shoudl return 0

I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.

I should also point out that most company names appear multiple times in column [C7].

a) Count the unique company names in column [C7]...

b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records

c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records

Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.

I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.

I know it is not trivial, but sure it is possible.

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

I have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.

I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.

I'm having a problem with coming up with a suitable excel formula. I have a table that calculates 2 values. Once these values are derived I need them to be compared to a separate table. This is how it works.

Table 1: Cell B8 gives me total weight: 3187pounds Cell C9 gives me the center of gravity in inches: 142.02

on sheet2 Table 2: Cell A2-A26 have weight increments every 50 pounds starting at 2200. Cell B2-B26 have the minimum C.G. limit Cell C2-C26 have the maximum C.G. limit

My question is how can I write a formula that takes the weight from B8 and compares it to the cells on sheet two (rowA) and finds the closest weight. Then from there finds out if my C.G. from C9 is within limits of my min and max values? Sounds complex but it's not that bad. I'll upload the file to my website and let people download if they'd like to see it.

I'm going nuts trying to figure out what formula I could use to give me totals in columns Q and R of the attached file. Basically, in the Weekly Totals column, I want to populate how many Customers and Bags were handled according to each ship name (the data is in the table to the left). Any suggestions would be GREATLY appreciated, I'm taking stabs in the dark but am not coming up with anything that works.

I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.

B C D OP00LKAOCC OP00LMRPCC OP00LVOFCC OP00LVOFCC OP00LVOFCC OP00LVOFCC

Here is my attachment with data in first sheet and expected output in second sheet. Have given comments in second sheet for better understanding.

Data is of a debt collection, in sheet column F "form #" denotes loan form numbers and column L "paid" denotes the amount received or not received.

In this Form # are unique and form number will repeat with different paid amount.

Entire row to be colored based on the sum value of paid column amount of respective form number.

1. non repeated single form number with negative value of paid - Orange color

2. repeated form numbers with sum of values in paid column as positive value - only entire row of form number with negative value in blue color

3. repeated form numbers with sum of values in paid column as negative value - first form number row to be colored with yellow and rest of repeated form numbers with purple color and its sum value (negative value should be made available in yellow line of the Form # group to the right of right most column data)

4. form numbers and paid column of value "0" - pink color

I tried recording and edited macro only for the logic of paid value "0" and confused with other logics and declaring variables...

I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.

I am trying to find a way of counting the total number of days medical devices were in-situ for a fairly large dataset. The worksheet has a few thousand rows. Column A - unique identifier for patient; column B - date inserted (Aug 02 - Aug 13); column C - date removed or audit date. There is no missing data, all rows have both dates. Some devices in for few days or weeks, some for up to 7 years.

I want to count each 12 month period (starting 1 Aug 02) the total "device days" for that year. (e.g. if device inserted 1 Feb 03 and removed 1 Apr 03, in year period starting 1 Aug 02 would have been in for 59 days. Another device inserted on same day and not removed until 9 Sept 05, would for first period (01/08/02- 31/07/03) 181 days, second period 366 days, third 365 days and fourth (01/08/05-31/07/06) 39 days). Therefore for the two rows, the first period total would be 240 days (59+181).

I have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.

Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab. -- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.

schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.

I want to be able to count the number of rows until I hit a blank row, and use the counter as an index. How is that possible?

I have a no. of rows that are fulled, followed by a blank row then another set of rows that are filled then a blank row etc. I want to count the rows filled till the blank row and set the counter to that number. Then I want to do the same with the next set of rows etc.

I would like to count items that appear in a list that will also appear in several rows. The list is also in no particular order. I have attached an example which may make it clearer than a wordy explanation.

I have in column AU cell 39 to cell 500 random sequences of 1-10 rows , with numbers in them, (as an result of formulas) I need to count the number of rows in each sequence that have numbers of 35 or less , but only up to the first number of 35 or higher. eg:-