Adding Another Condition To Sumproduct (countif) Statement

Oct 6, 2011

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.

SUMPRODUCT(COUNTIF(N3,"*"&References!$F$2:$F$34&"*"))

View 5 Replies


ADVERTISEMENT

Sumproduct OR Adding Criteria To Countif, Counta And Average

Feb 21, 2010

I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”

CURRENT FORMULA

I: “=COUNTIF(C2:C3100,"

View 9 Replies View Related

Adding Condition To SUMPRODUCT Causes Result To Go Up? (2007)

Jan 22, 2010

Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:

View 5 Replies View Related

Adding An Extra Condition To An IF Statement

Nov 17, 2009

=IF(SUM(Q4)=0,"",MAX(0,G4-MAX(F4,Q4))/7) - to calculate number of days for year

=IF(SUM(TODAY()>G4),(G4-MAX(F4,Q4))/7,SUM(TODAY()-MAX(F4,Q4))/7) - to calculate number of days year to date

I am using this condition to calculate the length of a service which falls with in a specific review period, eg

Service starts 1 Feb 09 and finishes 30 April 09

The review period is 1 Mar 09 and finishes 31 May 09.

I would need to measure the number of days that fall within the review period, which would be 1 Mar 09 - 30 April 09

At the moment I am missing a condition which identifies whether to subtract from 'service end date' or 'review end date'.

View 6 Replies View Related

Sumproduct Vs Countif

Jul 2, 2009

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.

View 6 Replies View Related

Sumproduct/Countif

Jun 29, 2006

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!

=SUMPRODUCT(--('Q2-PDR Query BW'!A1:A200="Yes"),--('Q2-PDR Query BW'!B1:B200="Health Net"),--('Q2-PDR Query BW'!C1:C200="Closed"),--('Q2-PDR Query BW'!U1:U200="<=45"))

View 7 Replies View Related

Countif/Sumproduct Function?

Mar 9, 2009

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.

View 3 Replies View Related

Using Weeknum With Countif Or Sumproduct

Feb 24, 2014

Essentially I have the following:

Sheet 1 (raw data)
column a = day
column b = weeknum
column c = Employee Name (5 names)

Sheet 2 (sheet I would like to create graphs)

I need to sum total records of each employee per week. For example:

column a2:a6 are each possible employee name
column b1:h1 are weeknums 1-7

I've tried countif but I'm only able to make that work if there's a helper column.

View 3 Replies View Related

Change Sumproduct To Countif(and(

Apr 8, 2009

Change Sumproduct to countif(and(. I'm using this formula

View 2 Replies View Related

Sumproduct/Countif/And/Or Query

Feb 14, 2008

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.

View 9 Replies View Related

Countif/sumproduct Formula

Oct 22, 2008

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.

I have this now:

=SUMPRODUCT(--(D2:D31719"7/19/2008"+0),--(G2:G31719>"5"))

but it returns a value of 0 which is incorrect.

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.

View 9 Replies View Related

Multiple Criteria Countif Or Sumproduct

Sep 16, 2009

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.

View 9 Replies View Related

Countif For More Than One Condition

Sep 18, 2009

I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that? Can a Countif formula be used for more than one condition or is there some other formula?

View 4 Replies View Related

Sumproduct Given A Condition?

Apr 7, 2014

I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.

A sumproduct would be best but the 2nd row of blue cells has an if statement in it.

View 3 Replies View Related

SUMPRODUCT Using A Condition

Jun 20, 2014

I would like to create a formula in D7 that calculates the weighted average growth for products (column A) manufactured by Bob (column B). In J5 I've inserted the expected result using a SUMPRODUCT function.

How to create a formula in D7 that will calculate from the data in B6:D6 the same result as in J5. i.e. How do I make the SUMPRODUCT function only consider data in columns C and D when Bob is the manufacturer?

View 2 Replies View Related

Sumproduct With More Than One Condition

Oct 23, 2007

Here is my dilemma I have 3 columns with data ranges two of which need to match certain criteria and if the criteria is met then I want the 3rd columns data to be sumed.

For instance: I want all only want to add column c for the ones in Column a that have blue and column b that have lost.

ABC
bluelost1
greenfound2
purplelost3
bluelost4
greenfound2
redlost3
orangefound4

View 4 Replies View Related

More Condition With Sumproduct?

Mar 3, 2009

I want to make some general tab from source in other sheet. I want to count unique product based more condition and also sum the amount for people and product.
It is better to understund in my atach.

View 4 Replies View Related

IF ELSE Condition With SUMPRODUCT?

Aug 7, 2013

I have a SUMPRODUCT formula that checks several columns of employee data for various conditions and sums up a salary value, which works just fine. But now I have to put a cap on the salary amount when summing up each employee, which I thought I could use an IF statement for, but alas just returns a #VALUE! error. Essentially what I tried was

Code:
=SUMPRODUCT( --( Range1=A1), --(Range2=B1), IF( SalaryRng>200000, 150000, SalaryRng) )

I thought since I was returning numeric values from the IF statement there shouldn't be a problem. Obviously not. So my question is whether an IF statement can be used this way, and if not, what method should I use? SUM/IF array formula?

View 5 Replies View Related

Using Multiple Sum Ranges In Sumproduct() & Countif() In Array

Jul 2, 2009

My problem is :

1.In G Column I put logic for Fail and Obtained Marks.

G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")

2. Now in H column I want use this formula which I obtained from this forum

H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1

To get the position of Students.

But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.

3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula ........

View 14 Replies View Related

Countif Or Sumproduct Function (count In A Range)

May 6, 2009

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.

View 2 Replies View Related

Using Multipel Sum Ranges In Sumproduct() & Countif In Array

Jul 6, 2009

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

View 9 Replies View Related

Index/Match From Sumproduct/Countif Result

Feb 27, 2010

I am using this to count how many times a particular number appears across numerous sheets, My sheets are in a Named Range MySheets....

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),A2))

This works great.
What I now need to do is to find ALL matches and return B1 from those sheets..

GHIJKLM1Countif ResultFirst ResultSecond ResultThird ResultFourth ResultFifth ResultSixth Result25AppleBananaPearMangoOrange31Banana43BananaMangoOrange

I have played around and the closest I can get is with this...

=IF(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),$A2),INDIRECT("'"&MySheets&"'!B1"))

This as you may know only returns the result B1 from the first sheet within MySheets.

View 9 Replies View Related

And Sum Countif Statement

Dec 17, 2013

I have attached a copy of my spreadsheet .My formula is in column BA and I am trying to figure out which students have 5 A* - C. We are only allowed to add 2 vocational subjects to this formula so basically 3 (or More) GCSEs + 2 BTEC/OCR Nationals. Now my formula works pretty well except if the candidate has 4 GCSEs and 0 or 1 BTEC it is still calculating as though the candidate has achieved 5 A* - C.

I have highlighted GCSE Subj in pink and Vocational Subj in Puple.

View 5 Replies View Related

Sumproduct Statement

Aug 21, 2008

I'm just wondering if I can expand on the following sumproduct formula:

=SUMPRODUCT(--($B$2='21_Jul08_NTH'!$H$2:$H$582),--($K$1='21_Jul08_NTH'!$O$2:$O$582),--(Misc_Inputs!$B$3='21_Jul08_NTH'!$L$2:$L$582),--(D7='21_Jul08_NTH'!$B$2:$B$582),--'21_Jul08_NTH'!$J$2:$J$582)*-1

I also want to add another criteria to say that only bring in data that equals the following "trade id's":
5578
5550
3437
3443
3440

At the moment I believe the sumproduct can only filter based on 1 criteria at a time and not an array of criterias?

View 9 Replies View Related

Sumproduct With Condition Formating

Nov 5, 2008

I have two columns from which criteria must be satisifed. column A denotes gender (m or f) and column B denotes a number of points. I want to sum those males who achieve >4 in one cell and those females who achieve >4 in another cell.

View 3 Replies View Related

Variable Condition In Sumproduct

Nov 25, 2008

I have the following formula

=SUMPRODUCT(INDIRECT($B3&"I3:I1000")*(INDIRECT($B3&"K3:K1000")>0)*(INDIRECT($B3&"u3:u1000")=$F$1)*(INDIRECT($B3&"p3:p1000")=$D$1)*(INDIRECT($B3&"r3:r1000")=$H$1))

Cells F1, D1 and H1 contain dropdown lists matching values in the referenced columns, PLUS they all contain the option All, which effectively means that the condition should return true.

I need something like if($F$1='All',1,(INDIRECT($B3&"u3:u1000")=$F$1))), but this doesn't work

View 9 Replies View Related

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

View 11 Replies View Related

2003: COUNTIF/SUMPRODUCT, Multiple Criteria W/Wildcard

Nov 24, 2008

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?

View 3 Replies View Related

CountIf Values With Between Statement

May 10, 2006

I have a list of 100 values in Column A of the attached spreadsheet Sample.xls. I need to count the number of values between a certain percentage including both positive and negative values. Example: I need to count all values that are between 0 and +5% and 0 to -5%.

View 2 Replies View Related

SUMPRODUCT Formula With Criterion Condition???

May 8, 2009

see the attached spreadsheet. Basically I want the formula in the yellow cell to carry out a sum product of two columns, but only for rows where the value in the first (criterion) column is 10.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved