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


ADVERTISEMENT

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

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

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

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

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

Countif To See Of There's A Same Value In Each Of These Ranges

Aug 28, 2008

I have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.

View 9 Replies View Related

Sumproduct With Different Ranges

Nov 5, 2008

I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me....

View 7 Replies View Related

Can SUMPRODUCT Be Used When Only Two Ranges Are Specified

Aug 7, 2007

I can't get the following formula to work...

SUMPRODUCT((Range1Range2)*(DollarValue))

Basically I have a master sheet of data:

Range1 DollarValue
AAA 20
BBB 35
CCC 5
DDD 10

and a separate sheet with items i want excluded from the calc:

Range2
BBB
DDD

I want the SUMPRODUCT to give me a value of 25 (AAA + CCC, excluding BBB and DDD from the calc). However, I'm getting #N/A in my formula cell. Is there an easy way to do this other than specifying each value to exclude, e.g. SUMPRODUCT((Range1"BBB")*(Range1"DDD")*(DollarValue))

I have a lot of values to exclude, so this could get very hard to manage...

View 9 Replies View Related

Countif On Nonconnected Ranges

May 14, 2007

I'm attempting to use the countif formula to make sure a number is entered only once in 4 nonconnected ranges, I492:I496, I499:I503, K492:K496 and K499:K503. I've tried everything I know and as I'm self taught and my teacher is an idiot, I'm not having any luck.

View 14 Replies View Related

Countif With Multiple Ranges

Jan 20, 2010

Countif: Is there a way to have a single criteria (a persons name) and multiple ranges example: a6:a10 c6:c10 and recieve the sum of that criteria and ranges?? I know there is I just cant get it.

I need to count a persons name entered in multiple ranges (cells or areas) on the same worksheet. I cannot make one big range because i will need to do the same for the b6:b10 but for a different "need" the a colum and c colum bieng "completed" the b colum bieng "not completed". I have tried =countif(a6:a10 + c6:c10, cell_with _persons_name) and for obvious reasons that wont work,

View 5 Replies View Related

Using COUNTIF With Separate Ranges

Sep 29, 2011

Is there a more efficient way to collect information from multiple ranges than using the COUNTIF and adding them together, such as

COUNTIF(Range,Criteria)+COUNTIF(Range,Criteria)+COUNTIF(Range,Criteria), etc?

I know my way around Excel, but I really do not understand much about VBA

View 1 Replies View Related

Countif With Two Ranges And Two Criteria

Jan 10, 2008

I am using a work sheet where I want to count if Column A has one criteria and column B has another
example
column B states is used to track contracts it can be vendor
column L tracks status it can be open, received, or closed

I want to count the cell if the vendor name matches and the status is open

also if that is possible is the same possible with 3 ranges and criterias?

View 9 Replies View Related

COUNTIF For Date Ranges

Oct 27, 2009

i had some formula provided by this site which seemed to work but is now resulting in #N/A or a zero value - can you take a look?

=SUMPRODUCT(('Report X'!$J$6:$J$10000="2")*('Report X'!$E$6:$E$10000>TODAY()-30))

Report X contains 2 relevant columns... E contains the date created for each item, J contains the type of each item - in this case i wish to see how many items '2' there are in the last 30 days

Report X is a daily report copied into the same tab each day - so the data will change every day depending how long each item is still in the system

this formula does work in the example sheet i provided but not in the spreadsheet in which i need it to work!

i also have similar formulas to calculate how many items are present in between 30 and 60 days exist, and how many over 60 days - all return the value "0"

column E containing dates is in the format 01/10/2008 - changed using Ctrl F - replace to amend it from 01.10.2008

View 9 Replies View Related

COUNTIF For Multiple Ranges

Oct 30, 2009

How to use COUNTIF when there are multiple criteria. For eg. I have 3 columns, and I want to count the # of employees in each row if all 3 criteria in columns C, D, and E hold true.

View 9 Replies View Related

Countif With Dynamic Ranges

Jul 1, 2007

Does COUNTIFS not work with named ranges/dynamic ranges? Everytime I try, I get a #VALUE! error. These are my 2 dynamic ranges:

ResultsM=OFFSET([results_list.xls]Results!$M$4,0,0,COUNTA([results_list.xls]Results!$M:$M),1)

ResultsQ=OFFSET([results_list.xls]Results!$Q$4,0,0,COUNTA([results_list.xls]Results!$Q:$Q),1)

The formula is simply:

=COUNTIFS(ResultsQ,"GENDER",ResultsM,"MALE"

However, this does NOT work. it returns the #VALUE! error. However, odly enough, when I use column references, it works:

=COUNTIFS([results_list.xls]Results!Q:Q, "GENDER", [results_list.xls]Results!M:M, "MALE")

This makes me think that COUNTIFS just don't work with named references..

View 8 Replies View Related

COUNTIF On Non Contiguous Ranges

Feb 22, 2008

I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10

In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")

View 2 Replies View Related

SUMPRODUCT Using Named Ranges

Mar 27, 2014

I am using the SUMPRODUCT formula below and it seems to be working

=SUMPRODUCT(('Attendance Data'!H:H="XXX")*('Attendance Data'!D:D="YYY"))

This, as I understand it is counting the number of times a row contains the text "XXX" in column H and also contains the text "YYY" in column D.

This seems to work for me.

Now my problem is when I want to change the formula to search for the text "XXX" in column H and also any text that apperas in a named range on another sheet in column D. The named range is called "New_Starters"

I am using the below formula which is returning N/A's.

=SUMPRODUCT(('Attendance Data'!D:D=New_Starters)*('Attendance Data'!H:H="XXX"))

View 4 Replies View Related

#Value! Error When Doing Sumproduct Of Two Ranges

Oct 2, 2007

I have a weird problem with some cells calculating (for example) =sumproduct(bundleNoNRP, bundleNoQTY). There are a number of cells with similar formulas calcutating with named ranges, and they all display #Value (a value of the wrong data type is used in this formula).

Both these ranges are set up via a VB program.
If i just seperately on the sheet do the sum of either of these ranges it works fine.

What could be going wrong? Is there some way of checking whats in the ranges? I cant see why the data type would be wrong if I can do sum calcs ok..Is there some rule which would mean the calculation wouldn't work anymore? This is part of a much larger VBA project so I hope I have provided enough information.

View 9 Replies View Related

Dynamic Ranges And Sumproduct

May 2, 2007

I'm having a fit with this formula; maybe the gurus will show me what I'm doing wrong. I have a list of area numbers along with dollar amounts that go with these area numbers. I have created a named range named "Areas" and another one named "MO_PD".

At the bottom of the worksheet I am doing a recap that takes the area number and sums all the entries in the MO PD column. This approach has been done before with Thomach's help. I'm trying to mimic it, but it's not working for me.

NOTE - I know that I can get around this using a pivot table, but I would like to use this approach for now. I would at least like to know what I'm doing wrong. Also, I'm not totally sure if I did the dynamic named ranges correctly so that may need to be fixed.

View 9 Replies View Related







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