# Conditional Counting With SUMPRODUCT

May 8, 2008

I want to know the formula which sums up volume and multiply it with given price to get the value by category. An example is given in the encl. excel file.

## Sumproduct.... But Counting?

Jan 14, 2010

I have a working SUMPRODUCT forumla, which sums the numbers in column B that are between two dates (column A) that I specify.

Is there a simple way to turn this into a count? I thought maybe a COUNTIFS formula, but that can't get the count between the dates, as only one criteria is applied to each range.

## Counting A Number In A Cell When Sumproduct

Apr 4, 2007

I have a spreadsheet with 3 coloumns. In A I enter Address, B Date, C Ward/District Area (this is a data validation list). At present I use; = SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2)). Summary B1 and B2 are dates to search between e.g 1/01/07 and 31/01/07. The above formula only counts the date enteries between B1 and B2.

I would like to change this so that it still checks that the date still falls between B1 and B2 but is adds together the numbers entered in coloum A. So coloumn A would become number of completed actions not an address.

## SUMPRODUCT - Counting Records - How Do I Not Count Dupes

Oct 19, 2007

I'm using the following formula to get a count of records that have a matching function name that is in B10.

=SUMPRODUCT(--('Oct-2007_FunctionSurvey'!\$T\$2:\$T\$1498=B10))

My Problem: There are duplicate entries in my recordset based on COl A (AppID).

How can I change the formula to only count the occurences of the B10 values based on a unique AppID (col A)?

## Excel 2007 :: Determining / Counting Number Of Values Used In SUMIF / SUMPRODUCT Function

Aug 2, 2014

I am using Office/Excel 2007 and Windows 8

I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.

Example:

Results for Row #2 gives: 95 with 3 values picked up under "Rate"

Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"

How can I count the number of these values referencing at the "Rate" like in the functions used.

I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.

I tried COUNTIF function, but allows only field/heading.

A
B
C
D
E
F
G

[Code] ..........

results for each formula used

95 formula: =SUMIF(B\$1:G\$1,B\$1,B2:G2)

95 formula: =SUMPRODUCT((B\$1:G\$1=B\$1)*(B2:G2))

3 formula: =COUNTIF(B1:G2,B1)

## SUMPRODUCT() Conditional Arguments

Jan 15, 2010

I'm trying to understand the consequences of conditional arguments for the function SUMPRODUCT(). For example, this function was kindly provided by SGH:

## Conditional Sumproduct Array Formula

Nov 1, 2013

I need to sum several non-contiguous columns if certain other columns meet a certain criterion.

Details:

in sheet "(1) 65539004 SAP"

IF

column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = \$B\$1 in "(4) Brand Breakout"

The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,

## Conditional SumProduct To Evaluate Iteratively

Sep 18, 2009

I have two tables, one summary sheet which contains a list of 30 projects. The second sheet has the projects broken down by task and status ("on time" or "late"), so it has three columns (project, task and status). I am trying to add up the number of projects within each task that are either "on time" or "late" and put these in two columns, so that the sum of each together will always equal 30.

The trouble is that any given task can appear multiple times for a project, and if one instance of that task is late, I want to count the whole project late for that task. Here is a table of what I am trying to do:

A1 MU On time
A1 MU Late
A1 FA On time
B2 FA On time
B2 MU On time
B2 MU On time

The desired result of this scenario is that under task "MU" I would show a count of 1 projects on time (B2), and one that is late (A1). For task "FA" I would show a count of 2 projects "on time" (A1 and B2) and none for "late"

I think that an array formula is needed where it assigns a 1 to a project and task that is on time, and a 0 if it is not, and then multiplies these figures for each project and adds them up.

## Conditional Counting

Jan 18, 2006

I have 8 columns.
The first column is a counter
The 7 others contains codes for faults on 7 different channels that get theres value from manual input.
The codes has a value and from all this i get a graph. Count/points.
Now i need to know when criterion is met and mark the count in the graph some way.

Fault codes:
A
B1-B13

For example:
Criterion 1 to be met: Fault B1 or fault A

Criterion 2: One individual channel >B1 or >two channels with B1 or > one channel with A

Criterion 3: One individual channel >B2 or >four B1

The criterion is judged by row. So when it find a row that match one of these i need that
count to be marked in the graph and then that criterion is set and not needed to be checked no more.

## Conditional Counting ...

Jul 12, 2006

Basically I am trying to count how many people are overdue on tasks and my spreadsheet layout is non-changeable. I have used the DATE function to calculate data in cells that I would like to count the occurrences if the result of the formula exceeds a criteria, but the cells I would like to count are every other row not continous where I could use something likeB3:B8. In the example I posted I would like a count where the result of the DATE function for cells B4, B6, and B8 results in a date less than today's.

## Counting Based On Conditional Format

May 30, 2007

i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.

when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.

the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database

what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.

## Counting Specific Conditional Formatting

Mar 4, 2007

I am looking to count the number of cells in a range c22:v22 that have bold red font, from conditional formatting.

## Conditional Formatting - Counting Color

Aug 13, 2007

Is there any way to count a list that has been highlighted by conditional formatting?

## Search Worksheets, Conditional Counting?

Mar 9, 2007

My workbook is made up of a number of worksheets, A master sheet, which creates new sheets for each work order I enter. Therefore, eventually I will end up with a large number of worksheets that I will have to sort through to find what I need. I put in a command button to allow me to search and hide all of the worksheets that do not match the the work order number I entered. However, if no sheets contain a matching number, then all sheets will be hidden, except for the master sheet. I want to add an exception that pops open a message box if no matches were found and stops the code. I was thinking of doing a conditional count to count all worksheets that match the criteria. If the count = 0, then the message box will open.

Private Sub SearchButton_Click()
Application. ScreenUpdating = False
Dim ws As Worksheet
Dim Search As String
Search = InputBox("Enter work order", "Search Archives", "")
For Each ws In ActiveWorkbook.Worksheets
If ws. Range("D4") <> Search And ws.Name <> "Master" Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub

## Conditional Counting Of Characters In Cells/Ranges

Sep 9, 2006

I have a spreadsheet, a small section attached. There are near 20000 rows at present. I need to count the number of characters in B1 excluding any hyphen in b1. If a hyphen occurs in b on its own,the count is zero.If b cell is blank,i need a count of all characters in A.I need a formula I can autofill down.The count column is c and I have completed it manually to show what I mean.

## Excel 2007 :: Conditional Formatting And Counting Coloured Cells?

Nov 1, 2011

i have a spreadsheet in excel 2007. It shows a students target grade in one column and their recent test mark in another column. Firstly i have applied conditional formatting to say whether or not the student has hit their target, below or above, using red, yellow and green colours. This all works fine.

Now i would like to add a formula that counts the number of cells that are red, yellow or green etc.....

## Excel 2010 :: Counting Cells With Interior Colors Set Via Conditional Formatting

Jan 3, 2014

How to sort out a way to count cells whose fill colors have been set via conditional formatting.

I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.

## Conditional Formatting Counting Characters If Less Than X Characters

May 13, 2009

I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.

I know the LAN function, but I don't know how to make it work for the conditional formatting.

## Counting Matching Values In Two Separate Ranges Without Counting Duplicates?

Jan 1, 2014

I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.

I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)

B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.

Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13

H1 Total of matching numbers in cell range K1:P11

## Formula Counting Attendance - Counting 1 Day Too Many

Feb 3, 2014

I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls

## Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

## Conditional Median Formula Into Conditional Quartile / Percentile?

Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!\$B\$15:\$DZ\$15=1000,DATA!\$B26:\$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!\$B\$15:\$DZ\$15=1000)

## Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

## Conditional Percentage Based On Conditional Sum

Mar 13, 2008

I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g

1400 156
1400 99
1400 74
1400 86
1400 256

The number of entries may vary for each block but is never more than 60. the next block may be e.g.

14:05 15
14:05 42
etc

and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............

## Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT((\$O\$9:\$O\$259="Active")+0)+SUMPRODUCT((\$O\$9:\$O\$259="Clearing Tmrw")+0) and

SUMPRODUCT(--(\$O\$9:\$O\$259="Active"),\$B\$9:\$B\$259)+SUMPRODUCT(--(\$O\$9:\$O\$259="Clearing Tmrw"),\$B\$9:\$B\$259) and

SUMPRODUCT(--(YEAR(\$A\$9:\$A\$259)=\$E\$4)*(\$O\$9:\$O\$259="Active"),\$B\$9:\$B\$259)+SUMPRODUCT(--(YEAR(\$A\$9:\$A\$259)=\$E\$4)*(\$O\$9:\$O\$259="Clearing Tmrw"),\$B\$9:\$B\$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

## Sumproduct..?

Jul 11, 2009

I have the following formula:

## SUMPRODUCT = #Value!

Mar 25, 2009

I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.

I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....

## How To Add Another Sum To Sumproduct

Jul 11, 2012

How to add another sum to this formula?

this is the formula i have at the moment

=SUMPRODUCT(--(\$AA\$21:\$AA\$6376

## Using SumProduct

Dec 4, 2007

Here's my question.
jan- feb- mar- apr- may
rep a 1 1 2 5 1
rep b 1 2 7 7 7
rep c 5 5 1 2 2
rep d 1 1 1 1 1
rep e 2 2 2 2 2
rep f 1 2 3 4 5
rep g 0 0 0 7 9

date date
March May

How do I set it up so that I will have two cells that will contain a month. Once the 2 months are selected it will give me the total for each rep during that time period? Do I use sumproduct?

## SUMPRODUCT #NAME

Aug 12, 2008

I have a report with a macro that imports a .TXT file into Excel then calculates the follwing formula. For some reason, before the .TXT file is loaded the formula is fine and has no errors, then when it is run I get a "#NAME?" error. I thought it might be because the formula changed during the macro but that's not it. Examples are below.

I've looked online and tried naming the ranges, but that doesn't work. I've tried the built in help but that didn't help. I've tried making sure the analysis pak is installed but that's not it either.

I've ran out of ideas now and you guys are my last hope. Any clues...??

Before macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=\$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=\$B3),--(Status="AWAI")))

After macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=\$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=\$B3),--(Status="AWAI")))