# COUNTIF And SUMIF Formula

Apr 20, 2007

It's been awhile since I've used excel formulas so I'm very rusty but I need to count the number of times I see a particular item in a list as long as it has another criteria as well. Example List and Result are below: I want to count the number of AAA Rewards that Alex has....

List
#DateEmployeeAmountReward Type
14/17/07Alex\$5.00AAA Reward
24/19/07Joe \$10.00AAA Reward
34/19/07Alex \$5.00 Store Certificate
44/20/07Alex \$10.00 AAA Reward

Result
Employee # AAA Reward Amount # Store Certificates Amount
Alex
Brian
Joe

## Nested Formula (Sumif / Countif) - Sorting Table

Mar 6, 2013

It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.

Here's the original formula:

Code:
(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)
/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)
/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)
/COUNTIF(H4:H35,"4")*4)/10

I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).

I tried creating a SUMPRODUCT out of it but it was just as bad:

Code:
=(SUMPRODUCT((C4:C35=\$O\$5)*(I4:I35=\$M\$5)*(H4:H35=1)*(G4:G35))
/SUMPRODUCT((C4:C35=\$O\$5)*(H4:H35=1)*(I4:I35=\$M\$5)*1)*1+...

And also this with sum(if)

Code:
=(SUM(IF(C4:C35=\$O\$5,IF(I4:I35=\$M\$5,IF(H4:H35=1,G4:G35))))
/COUNT(IF(C4:C35=\$O\$5,IF(I4:I35=\$M\$5,IF(H4:H35=1,G4:G35)))))*1+...

Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.

## SUMIF And COUNTIF

Mar 12, 2009

I want to calculate the average perofrmance % of 8 lines, the data isn't in one set of rows and some lines may not have values so I'm trying to account for this in my summary.

The code I'm struggling with is this...

=SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")

Dec 1, 2008

## How Do I Set Up A Countif Or Sumif That Is Multiconditional

Jun 20, 2006

I often try to set up a countif or a sum if that is conditional on two or
more conditions. For instance - I have Radio stations listed in one column,
flight dates in another column, number of contacts for each flight in another
column and appointments created from those contacts and finally, revenue
generated form those appointments. One example would be to sumif column one
equals the radio station and column two equaled the flight date then give me
the revenue.

## Countif, Sumif Or Sum Product

Jan 25, 2010

I have a table with 3 columns,

Project Person Hours

A B 1
A C 2
A B 3

So on project A persion B has worked 4 hours. I seem to be able to use sumproduct for counting how many times the person is in the project but I cannot seem to add on the final hours part.

## SUMIF/COUNTIF = Average

Mar 13, 2008

I have a report that has thirteen tabs. Tab one is Jan; two, Feb; three, Mar, etc. The last tab is where I'm consolidating all the data into a report.

In Row 2 of Jan-Dec are the days of the week.
In Row 30 I have the data.

I am figuring the average of each day of the week for each month as well as a total average year-to-date.

Here is my formula for Jan and Feb:
=SUMIF(JAN!\$2:\$2,"MON",JAN!\$30:\$30)/COUNTIF(JAN!\$2:\$2,"MON")
=SUMIF(FEB!\$2:\$2,"MON",FEB!\$30:\$30)/COUNTIF(FEB!\$2:\$2,"MON")

Works great after the month is over - but now I'm in March and the formula is adding all data form row 30 that falls under MON and dividing my all MONs even if it does not have data.

How do I nest in the COUNTIF statement to only count MON IF there is data in row 30.

## SUMIF/COUNTIF With 2 Criteria

Sep 24, 2009

I am trying to extract some data from a large spreadsheet and having problems...

Column C contains text descriptions, e.g 'Description One', 'Description Two', 'Description Three'

Column O contains a date.

I need to count the number of items that have a date prior to 1st April 2007 and have certain text contained in the full text string value in column C e.g. 'One'

## Count, Countif, Sumif

Jul 13, 2006

I've got a table that contains a lot of text that i need to summarse in another table. Its really just two columns the first contains a group name and the second a result. What I need to work out is the number of times the result occurs for each group. But every work sheet function I've entered fails.

## COUNTIF Or SUMIF: Changing Amount

Oct 29, 2008

I have a data range with 3 columns - A -employee number, B -charging number, and C -charge amount. "A" shows multiple times and must be associated with value from "B". I am looking for totals of "C" for any combination of "A" and "B".
Ex.

A B C
1 101 6
1 201 7
1 101 4
2 301 6
2 101 1
2 201 4
3 201 5
3 401 7 etc.

I am looking for totals (from column C) for A and totals for A with defined B. e. g.:

1 for all 101
1 for all 201
1 total
2 for 201 etc..

## Ignore Blanks Using =SUMIF/COUNTIF

Jun 2, 2009

I've come across a problem using the =SUMIF/COUNTIF function in excel. It incorrectly calculates blank cells as zeros.

=SUMIF(D4:D54,"Solar",T4:T54)/COUNTIF(D4:D54,"Solar")

The correct output should be 100%, but shows as 66.67% because of the blank cells in the column. How do I correct this?

## Weighted Averages Using SUMIF And COUNTIF

Jul 6, 2009

DATA: D1:D11 contains either blank cells or a dollar figure from a payroll check.
E1:E11 contains either blank cells or the date of the check.

Current Situation: Using the following formula I obtain a weighted average of the pay checks, not including the largest. =(SUM(D1:D11)-MAX(D1:D11))/(COUNT(D1:D11)-1)

Problem:
I desire to calculate the weighted average based on the checks issued only prior to a certain date. I have used SUMIF() in other parts of the workbook successfully but needing to withold the largest value has presented me with a problem I can not find a solution to. =SUMIF((E1:E11,">="&TargetDate,D1:D11)-MAXIF(E1:E11,">="&TargetDate,D1:D11))/COUNTIF(E1:E11,">="&TargetDate,D1:D11)

## Consolidating Lists And Nested SumIf(AND / Countif(AND Statements

Jun 10, 2014

I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.

I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.

However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.

I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.

## Combine SUMPRODUCT, SUMIF, COUNTIF Within Date Range

Jan 13, 2007

The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.

I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.

=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))

## SUMIF/COUNTIF- Several Numbers That Are Contained In Non-adjacent Cells

Jan 9, 2008

I have several numbers that are contained in non-adjacent cells. I need to create a cell that will evaluate the cells individually to make sure that they are within a certain range. Once that is done, I want the remaining numbers that are within the range to be averaged. How the heck can I accomplish this task? SUMIF and COUNTIF will not allow me to use non-adjacent cell ranges.

## Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

## SUMIF Formula That Is Collects Values From An IF Formula

Aug 26, 2009

Can anyone tell me why this formula is not working?

=SUMIF(\$D\$6:\$AL\$6,"=TM Goals",D12:AL12)

The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.

=IF(E12="A",\$L\$5,"0")

## Adding Today Formula In A Countif Formula

Dec 19, 2012

I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.

=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.

My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)

This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")

## If Then SumIf In Same Formula

Jan 18, 2009

I am trying to sum a column if Col B is equal to "TM" & Col A is equal to "Launch" then sum Col H. I am sure it can be done and I am hoping the right person reads this posts and enlightens me.

## Using AND/BETWEEN In A Countif Formula

May 27, 2006

my current formula is =COUNTIF('Input Page'!A2:A50000,"=Monday")

i'd like to change it to check what day is in the field and then only do the above formula if that day is within the past week.

so i need the "=Monday" section to be changed to read "(is equal to monday) and (is between today and today-6)" ...todays date will be taken from 'Input Page'!B2:B50000

## IF Or COUNTIF Formula?

Nov 5, 2008

See attached document, there are 11 cells in which will either contain Yes or No. Looking at the different combinations that there can be there can only ever be 9 out of the 11 cells being used or 10 out of 11 being used.

Also the last question (Row 25) could be filled N/A if this occurs I would like the formula not to count that. Is there a counting formula or IF formula which can be done to help me out?

## DSUM Formula Instead SUMIF Formula

Dec 19, 2006

I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example.
I like to know is there any advantage of using DSUM instead of SUMIF in this example.
The formule they used is =DSUM(J109:K113,2,\$M\$2:\$M\$3)
and the criteria is G,D
so they are adding all Debits in that column.
I changed the formula to =SUMIF(\$J\$110:\$J\$113,"D",\$K\$110:\$K\$113)
that of course does not need \$M\$2:\$M\$3 range criteria.

G 3458224.37
D 3401317.50
C 3401317.50
C 56906.87
D 56906.87

## How To Sum Up The Cells Which With (sumif) Formula

Apr 21, 2014

i am using "sumif" formula in cell C5 and C10, the next step is to add up these two cells (sum up the figures of "sumif"), then the answer will be +15+6=21, how to do with using single formula.

## SumIf Formula For Two Different Criteria

Dec 28, 2013

I need a formula which can sum the mode of payments separately occurring on different dates.

See the attachment. It is like

table 1
date cash credit card
1-1-14
2-1-14
3-1-14

table 2
product booking date booking amount payment date payment amount form of payment

a 1-1-14 100 2-1-14 100 credit card
b 1-1-14 150 3-1-14 150 cash
c 2-1-14 200 3-1-14 200 credit card

I want to add value in table 1 from table 2. In front of date CELL Both cash and credit card amounts should appear separately.

Book1.xlsx‎

## Sumif Formula - Greater Or Less Than

Feb 23, 2014

Below is my formula that works for a specific date. F\$8 is the date.

How could I change this if I want data b/w two time periods.

>=F\$6, and <=F\$8?

[Code] ....

## Sumif Formula: Three Ranges Add Together

May 14, 2009

The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula?

=sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$85:\$GA\$85)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$44:\$G A\$44)+sumif(DailySalesAnalysis!\$C\$1:\$GA\$1,A25,DailySalesAnalysis!\$C\$41:\$GA\$41)

## Tweaking A SUMIF Formula

May 30, 2009

The following formula finds the lowest five values in row 6 from column H to W. However if there are less than five values in the row the formula returns 0. I need to tweak it to return the smallest 5 values weather there is one or a full row of 16. Also if there are any of the same values I still want only the five lowest.

## Sumif Formula With 2 Criteria

Jul 24, 2009

is there away to do the sumif formula with 2 criteria? See attachment

## Sumproduct Or Sumif Formula

Aug 24, 2009

a1=good
a2 = 100

b2 = 50

c1 = good
c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

## Sumif/product Formula

Sep 29, 2009

I'm trying to do what I think its a simple formula with Excel 03 but can't get it to work. The info is in two work sheets - A&B.

[code]WORKSHEET A WORKSHEET B
A COLUMN 1 COLUMN 2 COLUMN 3 COLUMN1 COLUMN 2
B BLANK BLANK 10 BLANK DATE:1/28/09
C NAME DATE WORKED 12 NAME
D Jones 1/2/09 13 Bell
E Smith 1/6/09 12 Smith
F Cox 1/28/09 12 Cox
G Cox 1/28/09 10 Collins
H Collins 1/28/09 11 Jones

In worksheet B, Column 2, I need to pull the SUM of COLUMN 3 IF THE NAMES IN BOTH MATCH AND THE DATES MATCH

I've spent hours tyring to get this thing done!