# Countif To Find Total Sales

Jan 19, 2009

I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.

Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.

But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.

While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?

## Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

## Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

## Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

## Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

## Total Sales Of Every Month

Jan 14, 2013

I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.

The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.

So in the new sheet I will have

Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on

## Calculate Running Total Of Ticket Sales With 5 Different Groups?

Jun 17, 2014

I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.

## How To Create A Report To Show TOP 20% Based On Sales To Grand Total

Mar 5, 2012

Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.

How can I create a report to show the TOP 20% based on sales to grand total.

Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?

Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.

## Calculating Average Dollar Increase In Sales To Running Total

Jun 7, 2014

I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.

So basiclly if the Jan total is \$100 in column a, the Feb total is \$150 in column b, and the March total is \$200 in column c. I would like a formula that will tell me that the average increase in dollars is \$50.

## Pivot Table Calculated Field To Subtract Sales Between Two Periods (not Grand Total)

Sep 1, 2011

'Pivot Table Calculated Fields" - Below is my pivot table.

I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).

I know the following steps:

1) Click on the Pivot Table

2) Choose "Formulas"

3) Choose "Calculated Field"

4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)

5)......then I don't know what to do

Sum of Quantitymonthsupplier idjanfebGrand Total
754466163150842519337568263901785022875682639410025035075682639500
75682639642523866375682639780012492049756826398209209756826399292292
756826400264264756826401178178757494037292292761034919209209867233456264264(blank)
Grand Total245154236931

## Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.

May 23, 2006

I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

## Count Each Month (Using Countif To Total Employess)

Oct 29, 2008

i have a list of employess who i need to count each month. i need a way i can say count if >0 for month = Oct. each employee has a summary of there work for a month in a list going down.
example

___John
Jan__0
Feb__19
Mar__50

___Bob
Jan__4
Feb__56
Mar__12

Right now i have a way i am summing up the total work for all the employees
"=SUMPRODUCT(B21:B512,--(A21:A512=A4))" <------'A4=Jan in the formula'

How could i change this to reflect count the employees and not sum the total? The idea is a need to know how many employees had a number greater then 0 for the month of Oct.

## COUNTIF Formula: Gives A Total Of How Many Names Are In That Range

Jan 5, 2009

I have certain cells in column A2:A22 that have names of people. I want a formula in Cell A23 that gives me a total of how many names are in that range. I know this is simple, but how do I put my criteria that if a cell is not blank to count it?

## COUNTIF Formula: Total Of The Number Of Cells?

May 24, 2006

I have cells C22:C27 with a number that could change each week. How can I get a total of the number of cells that = 0,1,3,4,5,6,7,8,9,10,11,12?

## Countif Or Vlookup: Get The Total Count Of New And Active-to-date

Oct 15, 2009

I have two columns - Employee and Status of Deal. Now, I need to get the total count of New and Active-to-date but on a per employee basis. Am I making any sense? To clearly put it, I need a formula that will answer the questions below:

How many New and Active-to-date deals does Michael et al have? I tried to use Countif but that gave me the total number of deals, not on a per status basis. Will VLookUp work or a combination of both? If so, how? I'm using Excel 2003.

## Sales Summary Sheet - Consolidate All Data In Sales Order Number Wise

Jan 5, 2012

I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....

All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.

Column A Column B
201 \$200
202 \$300
203 \$450

## Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales

Jul 2, 2009

I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

## Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

## How To Use Variation Of COUNTIF Function To Generate A Total Number Of Occurrences

Feb 21, 2014

I'm trying to use a variation of the COUNTIF function to generate a total number of occurrences. The RANGE is a singular column on each of five tabs in a spreadsheet, but the CRITERIA (hopefully) is made up of two separate cells on one tab of the same spreadsheet. Here is the formula as I have it written:

=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","8),countif('Tuesday-No Shows'!D:D,"*" & A2 & "*"),countif('Wednesday-No Shows'!D:D,"*" & A2 & "*"),countif('Thursday-No Shows'!D:D,"*" & A2 & "*"),countif('Friday-No Shows'!D:D,"*" & A2 & "*")))

The purpose of this formula is to check to see how many times a student does not show up for an appointment over the course of their time with us. We have one tab with all the pertinent info on each student and I'd like to use this formula for a "No Show" column to more easily know when to cancel a student's services. The problem is that I can only get the function to search for one criteria right now. I've tried using the OR function and adding it to the current function but it always gives me 0 as the result, but if I take out the OR it returns me a higher number. Here's what it looks like with the OR added:

=if(A2="","-",sum(countif('Monday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Tuesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Wednesday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Thursday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*")),countif('Friday-No Shows'!D:D,or("*" & A2 & "*","*" & B2 & "*"))))

A2 designates the students first name and B2 designates their last name. If I eliminate the OR and resort to multiple COUNTIF's, it will generate twice the number of responses that I want, I fear.

Example.xlsx

## Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be \$85,000 and my currently monthly sales must be \$7,000. I have a sheet that tracks the \$85,000 and tells me what I need to achieve that, but I haven't figured out how to include the \$7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the \$85,000 rolling 12, but I actually need to hit \$4821.79 to meet the \$7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

## Find Format: Find Method And Combine It With A Countif Or Loop

Feb 20, 2007

I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.

Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub

## Summarize Monthly Sales From Daily Sales

Jan 5, 2010

I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

## Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 \$xxxx
3/5/2005 \$xxxx
3/20/2005 \$xxxx
4/2/2005 \$xxxx
4/10/2005 \$xxxx

Into this:
March 2005- \$xxxx (monthly average)
April 2005- \$xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

## Get 5 Top Sales People Names And Their Sales?

Oct 3, 2012

In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.

Starting in cell B59 and C59 I'd like to display their name and sales respectively.

Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.

## Pick Range Of Dates And Find Number Of Days Without Sales Between Those Dates

Feb 23, 2014

Here is my set up:

A2 to BF2 is a range of dates

A3 to BF3 are sales. Days without sales are 0.00

I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.

## Countif To Find In Between Data

Oct 9, 2009

[data] ...

I want to put a count to column 4 (weight) top know no of packets with wieght beteen 1.5 and 2 kgs. How can I do this with a count.

## Sum The Total And Find The Average Cost

Oct 1, 2008

I need a formula that will scan column A (Code)total the like items (also) add column B (Qty) if there is a number greater than 1. Then add the price (\$) together and divide by the sum of A&B.

In other words find the average price for the total of each item..

A B C
Code Qty \$
PH06003000 1 1504.8
PH06003000 1 1582.24
PH06003000 1 1606
PH06003000 1 1504.8
PH06003000 2 3009.6
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06003000 1 1504.8
PH06024000 1 2499.2
PH06024000 1 2499.2
PH06024000 1 1896.07
PH06024000 2 3909.66
PH06024000 1 2240.7
PH06024000 1 2259.4
PH06024000 15 30030
PH06024070 1 2039.4
PH06024070 1 1958.66
PH06025670 1 2521.2

## Find Out Total Duration Logged In

Oct 18, 2009

is it possible to calculate the total duration of logged in from the data below?

Column 'A' has the time and extn# on column 'D' and indication of logged in or logged out on column 'F'

there can be several log in and log out by an extn.

## How To Find Percentage Of Total For All The Items In Different Categories

Dec 17, 2013

Formula that can calculate % of total for individual items. For example,

Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185

I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)

## V Look Up: Find User Number 1 And The Total For Weeks 1, 2, 3 And 4

Mar 13, 2009

I have a large spreadsheet with various data. On the first column I have a list of user ids, this is a unique id to each user. In each row there are data there are varies other data. I want to find a unique id and then add up the figures from week1, week2, week3 and week4.

So lets say I want to find user number 1 and the total for weeks 1, 2, 3 and 4, how would I do that using an excel formula? I have given an example below but I have simplified to spreadsheet.

IDWeek 1Week 2Week 3Week 4
139424236
255556044
334335434
461515350
529504694
618243830