Tracker
HOME    TRACKER    Excel

# Criteria Values In COUNTIFS Function

## I have the following formula in a sheet to record basketball stats that I am putting together. =COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss") This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot". My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time.

Related Forum Messages:
Countifs With References As Criteria
I'm trying to use this formula in a cell but it keeps giving me #NAME.

What does this mean?

Month Criteria In Countifs ????
check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).

I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........

Countifs Function
I am using the COUNTIFS function. I can us it with simple criterias but unsure to go about this criteria.

I would only like to count the cells if the range in question is equal or greater than S3 but is equal or less than T3.

I don't know how to use the => signs.

Countifs With Month And Year Function
I have a column titled "Start Date", "Month/Year", &"Total". I want to count the number of dates that appeared in the "start date" column that have the same month and year of the "month/year" column. That count should be in the total count. For ex, I want the first total (april 2008) to be 1, the second (may 2008) to be 1, and so on. I tried using countifs month and year of the start date is the month and year of the month/year column. Not working.

Here's my data: ....

COUNTIFS And Paste Special Function
I have a question regarding COUNTIFS. I have data arrayed vertically and horizontally on a worksheet. Date - Column 1 Column 2 etc. Criteria data (flight information) is listed under these columns. I'm trying to get the COUNTIFS formula to count the number of times a particular flight occurs within a date column.

My formula is

=COUNTIFS(C27:G24 (this is the field that contains the flight info), C6 (this contains the particular flight I'm looking for), C16:G16 (this contains the date range, C5 (this contains the date I'm looking for)

When I use this formula I get the result #VALUE! back. I also wanted to ask if there is any way to use the paste special function but not lose the original formatting/formulas from the cells one is pasting from.

Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))
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.

Convert Absolute Values To Negative Values Based On Criteria
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"

Below is a small sample of data, real data is around 100 rows

Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323

Replacement For COUNTIFS
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.

Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?

Countifs On Two Different Variables
Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.

How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.

At the moment I have this:

Countifs On X Axis
I'm looking to count the following:

A B
a 1
a 1
b 0
b 1
c 0
a 1

If column A=a AND column B=1, add column B on the same axis. I can only get a function that adds all B column if A=a. The function should equal 3. Cells B1, B2 and B6 should be the only ones counted.

Multiple Countifs
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,

Nested IF And COUNTIFS Functions
I would like Excel to look at column A, and if there is a number0, count it and go to the next row. If it's =0, then go to column B and see if THAT number is 0. If it is, count it; if not, go to C. So my data looks like:

A B C
0 2 3
4 0 0
0 0 0

I only want it to count one time per row, so right now I am using =IF(COUNTIFS(A2:A30,"0",B2:B30,"0")0,COUNTIFS(A2:A30,"0",B2:B30,"0"),IF(COUNTIFS(B2:B30,"0",C2:C30,"0")0,COUNTIFS(B2:B30,"0",C2:C30,"0"),0)))

I also tried it with a SUMPRODUCT to simplify but both formulas are getting me the wrong answer. I'm using Excel 2007.

Date Range And COUNTIFS
I need to look at a series of data once per week and calculate the results. I'm trying to use COUNTIFS to get a result based on both the date I'm doing the analysis for and for the response.

I've attached a sheet that shows a sample of what I'm trying to do. Cell B1 contains the date range I'm using, and cell B18 shows the formula I'm having trouble with. I suspect it's because I'm trying to treat a date like text, but I'm not sure.

Replacement Of COUNTIFS Within 2003
Assist me in getting the following statement into Excel 2003 format - it is working OK in Excel 2007.

=COUNTIFS('Sheet 1'!\$AB:\$AB,"True",'Sheet 1'!\$F:\$F,'Sheet 2'!B13,'Sheet 2'!\$J:\$J,">="&H2,'Sheet 1'!\$J:\$J,"

COUNTIFS With Date Range
I need a COUNTIFS function (I think that will be the best function to use at least).
I have a two columns of data, column one is dates (mm/dd/yyyy) and column two is states (AK, MA, MI, etc). I need a way to count how many times a states comes up for a certain month. For example, I need to know how many times MA appears through the dates 1/1/2007 and 1/31/2007. Here's what I have so far. It works but I can only specify one date. =COUNTIFS('SHEET NAME'!D:D, "1/5/2007", 'SHEET NAME'!E:E, "MA")

Converting COUNTIFS To 2003
I have the following excel formula:
=LOWER(IF((\$H2&":"&\$M2&":"&\$N2&":"&TEXT(COUNTIFS(\$H2:\$H\$2,\$H2,\$M2:\$M\$2,\$M2,\$N2:\$N\$2,\$N2),"0000"))=": ::0000","",\$H2&":"&\$M2&":"&\$N2&":"&TEXT(COUNTIFS(\$H2:\$H\$2,\$H2,\$M2:\$M\$2,\$M2,\$N2:\$N\$2,\$N2),"0000")))

I need to convert the COUNTIFS into something that is Excel 2003 friendly.

COUNTIFS With A Variable Condition
I am using COUNTIFS to work a formula for finding an average of a SUMPRODUCT. Formula:

Converting Countifs To 2003 Formula
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.

Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!\$S\$2:\$S\$9806, where 'Data_Q2 2008'!\$B\$2:\$B\$9806 = 'Metric Upload'!C2.

'Data_Q2 2008'!\$B\$2:\$B\$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.

Multiple Countifs With Date Complication
I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date.

Example:

A B C
John 1 02/07/2009 09:45:10
Peter 1 03/06/2009 10:14:12
Fred 1
Peter
John 1 02/07/2009 09:45:10

How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?

Array Or Countifs For Counting Survey Responses
I have recorded the data from 12 records (i actually have hundreds, but im just using a small sample). Each record is represented by a vertical column. I want to find the total number of "x's" for only the records from Country1 (from all the answers). Then I want to find the number of "x's" from only Country2. I have tried the "countifs" function but cannot find a way to make it work. Would a different array function be better? How would I write it?

P.S. The answer for Country1 should be: 15.
The answer for Country2 should be: 5.

Multiple Criterieas Using Sumifs And Countifs Not Working
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:

If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)

I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.

BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B \$ 278 4AustriaExternalNoneExt3C \$ 900 5AustriaExternal-8A \$ 2,388 6NorwayInternal-MV \$ 567 7AfricaExternal-SY \$ 1,234 8AfricaInternalNoneIntJN \$ 908 9TOTAL \$ 6,275

Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)

And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.

Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.

Countifs Alternative On 2003 With Multiple Cells And Worksheets
I created the following in Excel 2007 but need to come up with a replacement formula in 2003.

=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")

With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.

In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.

If Function With Range And Two Criteria
I have the following formula below to return "C" if the value in the cell is "C", and nothing if not.

I want to expand it to return "C" if the value in the cell is "C" or "D", for a range between C2:H2.

=IF(C2="C","C","")

I'm have trouble with this easy adjustment.

IF Function With Multiple Criteria
MathScienceLanguageOverall
PassPassPass----
PassFailPass----
PassFailFail----
FailFailFail----

What formula should I use to show "Pass" if a student passed all three subjects, "FAIL" If student did not pass in any subject and "TRY AGAIN" if he passed at least one subject? I can't seem to accurately plot "IF" formula. Should add in "AND" / "OR" as well?

Function Using Multiple Criteria
Bid Manager Probability of Success Total Fee

cp 25% 675,000
rz 40% 18,383,541
nw 50% 8,700,000
cp 10% 4,500,000
ap 50% 1,200,000
kd 50% 49,500
sl 25% 3,200,000
bc 20% 310,000
sl 20% 3,460,000
nm 30% 6,750,000
at 50% 23,000,000
ap 30% 367,500
rn 40% 13,750,000

I have a list of commodity and percentages of success with fees in the 3rd column
I need a function to count the records with multiple criteria e.g greater than 20% but less than 30%.

Second i need a function when it complies to previous formula it should give fee total pertaining to that count.

Frequency Function With Criteria
I have a condition that it only applies to criteria before a certain date but I cannot get it to give me the right answer.

Original Formula ...

Subtotal Function With Criteria
I would to use the subtotal command and have it exclude rows from another column in addition to obeying an autofilter:

A B C
1 Area Rep Name Amount
2 Quota John 100
3 NW John 200
4 NW John 300

I want to use the subtotal(9) on the amount column, but I don't want it to include rows where the value Quota is in the area column. The idea is that the amount for a given autofilter condition (rep = john) should only show his forecast amounts but not his quota number for his area. DSUM includes hidden row as does sum

I have tried

subtotal(9,sum(C2:C4*(A2:A4<>"Quota")))

but that does not work

=SUM(C2:C4*(A2:A4<>"Quota"))

works but does not let me filter the data to only show values for john versus all reps

Offset Function With Criteria
I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?

Sum Function With More Than One Criteria
I have a large transactions table with 10 columns and more than 7,000 rows. One of the columns names the country in which the transaction has taken place (array named "country")

Another names the year of the transaction (array named "year")
And a third one holds the transaction value. (array named "value")

What I would like to do is to sum the transaction value of one country for one specific year? I.e. what was the aggregate transaction value for transactions in Sweden in 2005?

COUNT FUNCTION FOR A FILTERED CRITERIA
In an excel sheet A1 to ...... colum i have some cirteria. If I filter a criteria i should get the count for it. Kinldy let me know with out using VB how is it possible.

Database Function Criteria Range
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B\$9:B\$10000)*(A2='Exp Rpt'!E\$9:E\$10000),'Exp Rpt'!H\$9:H\$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.

COUNTIF Function With Multiple Criteria
I need to count rows that meet 2 criteria.
I have seen this help page
http://www.ozgrid.com/Excel/count-if.htm
but that counts rows with "criteria 1" OR "criteria 2"...
I need to count rows that fulfill "criteria 1" AND "criteria 2"
ie - count the rows that have todays date AND a cell that says "COMPLETE"

ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???

Average Function Based On Criteria
I'm trying to find the best Macro or Formula to get this done.

Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".

Use IF/Lookup Function In Multiple Criteria
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)

I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )

excelformula.gif

pricing.xls

Countif Function Work On Multiple Criteria
Can countif function work on multiple criteria to look on?

this are the criteria.

SA SL 1.0SA SL 0.5SA VL 1.0SA VL 0.5SA SLWOP 1.0SA SLWOP 0.5SA VLWOP 1.0SA VLWOP 0.5SUSPRD

Advanced Filter With Function As Criteria Range
I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2).
I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like:
If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.

I need to check all 2500 values in all 6000 rows.

I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.

YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter.
But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".

Function To Search For 2 Sets Of Criteria As Opposed To Just 1
So here is my little dilema,

I have a worksheet that I need help with using SUMIF function.

Basically how do i use this function to search for 2 sets of criteria as opposed to just 1.

SUMPRODUCT Function With Date Calculation Criteria
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.

One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.

User Defined Function For Summing Multiple Criteria
I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.

IF Function Return Specific Criteria For True And Then For False
I need to check one column to see if it is less than another one
If it is I need to have the IF function return specific criteria for true and then for false but then I also need it to rely on the results of another question from another column if the result of the first query is less than the figure in another column again I need to return specific criteria for tur and then for false
An AND function requires both to be true but when one isnt what do I write?

Locate Date Function/Formula Based On Criteria
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference

Multi Conditional IF Function: Show Where The Tender Ranks Against A Set Of Criteria
I have a workbook which scores tender's, the calculations behind the front sheet work fine, I would now like to show where the tender ranks against a set of criteria. I have attached a sample and typed into the cell D12 what I am trying to with this list of criteria below starting in cell C15.

Dcount Function/Formula: Count Based On Two Different Criteria In Adjacent Columns
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.

Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this

jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET

and I want to have a count of how many RET's there are for Initials JP ect

Sum Values That Meet Certain Criteria
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.

I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.

I have worksheet where I would like to apply a number value proportionately throughout a column bases on user-defined conditions (i.e. >=, <=, Between) from other colunmns. See example below and attached worksheet with further detail.

Col A Col B Col G Col L Col M
01380Employee 15.0 5.5 (0.8)
00304Employee 24.0 - (0.8)
01901Employee 33.8 (1.0) (0.8)
01729Employee 43.0 - (0.8)
01482Employee 53.0 (1.1) (0.8)
00299Employee 62.5 - -
00069Employee 72.5 0.5 -
01783Employee 81.5 - -
02076Employee 91.0 - -
01934Employee 100.5 - -
TOTAL 4.0 -4.0

In the above example, I would like to have the TOTAL value of Column L (4.0) be inversely spread throughout column M based on criteria selected by a user in a section of the spreadsheet.

Criteria:

Column G >= 3 AND all values in Column L = 0; Spread -4.0 (Inverse of cell L12 = 4) in column M for records that match criteria.

I would like to have a command button apply the logic of the conditional criteria set for Columns G & L and spread proportionately ithe value from n Column M.

I added examples of the different criteria and the layout in the file attached.

Values In Ranges As Criteria For Formatting
I have a spreadsheet where my dropdown menu is taken from a master list. The master list is also broken down into several catagory specific lists.

I need a macro that will take the individual cells in the worksheet that use the master drop down list, check them against the contents of the specific lists (defined names) and assign a cell color fill based on wich list the data was found in.

lets say my master list is:

MASTERLIST
apple
orange
bananna
lemon
pork chop
sirloin
chicken wings
carrot
potato
onion
sage
thyme.............

Sum Unique Values Under Criteria
With reference to a thread that is nearly similar to my own problem:

http://www.excelforum.com/excel-gene...ml#post2076546

I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".

Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.

Repeating Criteria Between 2 Values Using MAX
I'm having trouble delivering a MAX value within a range.

In column A I have a running list of numbers, in column B I have a yes or no answer and in column C I'm trying to write a formula that will deliver a MAX value from the A column inbetween occurancies of the Yes and No values from the B column.

I'm trying the SUMIF function but it seems to be proving problematic.

This is what it would look like.

A B C
1 Y 1
2 Y 2
3 Y 3
2 Y 3
1 Y 3
2 N 2
7 N 7
6 N 7
5 N 7
3 N 7
2 N 7
6 Y 6
9 Y 9
3 Y 9

It is almost as if I'm trying to get the formula in the C column only find the latest MAX value from where it currently is to when the last changeover happened from Y to N.

I can't find the Max dependent on a yes or no answer, but I'm struggling to make it only look at the last batch of Yes's or No's!

Sum The Values If You Have 2 Criteria And 2 Look Up Ranges
I need a formula that sums the values with 2 different criteria and 2 different look up ranges