I have a worksheet where I am trying to count the number of occurences of several text strings.

For example:

I'm trying to count how many times "paid in full" and "fully paid" occur in column A.

I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)

Here are my formuals (I didn't write either of them, another co-worker did)

=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))

=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))

Also, if there is another and easier way to do what I'm trying to do, I'd love to know.

I am working off a seperate worksheet and trying to use a Sumproduct with multiple criterias along with one criteria that calculate all fileds that =<45. The formula I am using is listed below. I get #VALUE!

I am trying to count number of staff working in any day without deleting the empty cells.(see attached sample sheet). I tried sumproduct as I have two criteria but it is giving me a "zero" result. I only want to count cells in range B3:B32 that does not have "#" sign if there is a staff name if there is a staff name id corresponding cell in Column A within A3:A32.

a calculation that has some conditions. I have a spreadsheet where I am calculating how many cells fall within a month date range using forumla :=SUMPRODUCT(('Project Team Actions'!J8:J525S2)) - as it calculates via other worksheets, but I only want it to count the cells that have OPEN or On Going in a neighbouring column, but not count the items that are closed.

I have a sheet with repeated dates for several months and I need to break out data by week and then by certain criteria. I can do 1 or the other but combining the COUNTIF formula and the SUMPRODUCT formula has proven to be beyond me.

What I need to do is have the formula return a sum of all of the fields in colG that are >5 within a date range. Once I find that # I have to divide it by another field and multiply by 100 to get the percent.

I haven't been this deep into excel before. The deeper I look, the more potential I recognize, the more amazed I get. That being said, I have come to a tough count issue. Let me attempt to explain as precisely as possible.

My current worksheet is large but I am only particularly concerned with two columns of information (Regions) and (Days). The logic I am attempting is something along the lines of Count If Region = East, or West, and Days is greater than 0, less than 60.

I am open to any and all suggestions on how to tackle this situation. I have been able to achieve similar counts by using pivot tables but the dynamic nature of these two columns presents some difficulties that my “new user” mind has been unable to work through.

I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.

I am using the formula below to count the occurences of relevant text strings (names) in a cell that can contain many separate strings. It works great. "References!$F$2:$F$34" contains my list of names and the formula returns how many occurences there are in each cell.

What i'd like to do know is work in a date criteria. Each name has an associated relevant date attached to it as does each cell that I am looking for these names in.

So...in english what Id like to do is alter my formula so that when it finds a text string that exists in "References!$F$2:$F$34", it then compares the associated date in "References!$G$2:$G$34" with the date associated with N3 which happens to be O3.

I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .

1.In G Column I put logic for Fail and Obtained Marks. G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail") 2. Then i made array formula in column H to get the position ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1} Some improvement to write fail & position ={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)} And more work to get total marks & position and Fail at the same time...... ={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)} Now I wounder that is there a way to do all calculations in column H without using column G

By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula ={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}.....................

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.

I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?

I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.

For instance: If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.

Sam Douglas:BI Sam Douglas:BI Sam Douglas:SI Sam Douglas:BI

I have following data (two columns Parent and Child), now I want to apply Countif on Child cell. But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.

I'm reasonably new to Excel, and have a fairly basic question to check out:

I have been using the COUNTIF function to count up numbers of items in various categories in a column.

The formulae I have been using are like this: =COUNTIF(F$3:F$201, "Red")

or where I've wanted to combine various comments =SUM(COUNTIF(F$3:F$201,"Yellow")+COUNTIF(F$3:F$201,"Cream"))

I'm not sure what formulae to use to count up 1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)

2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values

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.....

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?

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")))

I'm working on a project on predicting future cash flows from loans we have made and have ran into a wall as i can't figure out the formula to achieve what i would like to do. I've gotten a lot of help from you guys in the past and would appreciate any help you may be able to provide for this problem. So here it goes. First, here is how i have the spreadsheet setup.....

I've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my Function weight(Aref As Range, count As Integer, Bref As Range) As Variant

Dim i As Integer Dim top As Integer Dim vA() As Variant Dim cA() As Variant Dim vR As Range Dim cR As Range

This problem is I keep getting a #Value! error when I place the date section into formula. I have check to see thatthe date format is correct and as far as I can tell it is EXAMPLE: 06/12/2007

This formula did work in a nother worksheet, thus the confusion Ihave

The other issue is how to get two different text items in the "HOW" group to be True for Formula to pull requested values???