• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIF + cumulative SUM formula: Can they be combined?

C

chloec

Member
Having trouble figuring out this formula. I need to figure out how to add the data below with a countif formula and make the data series cumulative. Please note the data is always going to be a rolling 12 months...but I have space constraints in this text field.

11-Feb 11-Mar 11-Apr 11-May 11-Jun 11-Jul 11-Aug 11-Sep 11-Oct 11-Nov 11-Dec

0 3 0 0 1 0 1 1 1 4 0


To get the above data, I created a formula that uses =COUNTIFS. The formula brings in data from another tab. here's an example of the formula in cell 11-Feb and 11-Mar:

=COUNTIFS(Supplier,DELTE!$D$5,Status,"Pending",Mo.Formula,"2",Yr.Formula,"2011")

=COUNTIFS(Supplier,DELTE!$D$5,Status,"Pending",Mo.Formula,"3",Yr.Formula,"2011")


My question is simple for an excel ninja...I need to add an additional calculation to bring over the sum from the previous month and add it (cumulative).


For example - once I figure out this formula, my =COUNTIF data will add up like this (compare values below to values above. All I did was add the data cumulatively)

11-Feb 11-Mar 11-Apr 11-May 11-Jun 11-Jul 11-Aug 11-Sep 11-Oct 11-Nov 11-Dec

0 3 3 3 4 4 5 6 7 11 11


Thank you very much for the help!
 
Just add whatever is to the left. Assuming the Feb formula is in B2

=COUNTIFS(Supplier,DELTE!$D$5,Status,"Pending",Mo.Formula,TEXT(MONTH(B1),"@"),Yr.Formula,TEXT(YEAR(B1),"@"))+A2

Note that I replaced the "2" and "2011" with functions so that you can drag it to the right w/o having to manually change.
 
Thank you. I wasn't sure how to add to Countifs. Seems very easy. Can you please explain to me the logic with the functions - what does the "@" mean? Thanks again!
 
Glad I could help. The sections I added look like this:

TEXT(MONTH(B1),"@")

the month function returns the numerical value of the date in B1, which will be 2. However, your formula was setup to expect a text value of 2. The TEXT function is used to format a value, using the same nomenclature as you use when formatting a cell. (e.g., #.00 to show 2 decimal place number). The @ symbol is what XL uses to indicate "format as text", so the TEXT function is taking the number 2 and changing it to a text string of "2".
 
I have another question - I've tried to research to no avail. I'm using COUNTIFS formula. I have a list of items in criteria_range1, I want to count certain items and exclude others. I've been using formula below, but it doesn't work as it appears trying to use multiple criteria in a range is not acceptable...or is it?


Examples:

=COUNTIFS(Students,{"Sam","Lisa"},Status,{"Pass","Fail")

Expected Answer: 4


Status Student

Pass Sam

Fail Lisa

Fail Tom

Pass Jack

Absent Jason

Absent Sam

Fail Lisa

Pass Tom

Absent Jack

Pass Sam

Absent Lisa

Fail Tom

Pass Joey
 
Correct...the COUNTIFS is looking for criteria along the same row. Usually for OR type logic, we have to use an addition of something. Perhaps this will work?

=SUMPRODUCT(((Students="Sam")+(Students="List"))*(Status<>"Absent"))
 
Hi ,


So also will this :


=COUNTIFS(Students,"Sam",Status,"<>Absent")+COUNTIFS(Students,"Lisa",Status,"<>Absent")


When you have multiple criteria specified in a COUNTIFS formula , it is an ANDing of all the criteria , as Luke mentions ; when you need to have an ORing of multiple criteria , you need to use two or more COUNTIFS functions within the same formula.


The above works only because there are only three statuses possible ; so instead of checking for "Pass" or "Fail" , we can check for not "Absent" ; if there were four or more statuses possible , and you wanted to check for "Pass" or "Fail" , multiple COUNTIFS would have to be used :


=COUNTIFS(Students,"Sam",Status,"Pass")+COUNTIFS(Students,"Lisa",Status,"Pass")+COUNTIFS(Students,"Sam",Status,"Fail")+COUNTIFS(Students,"Lisa",Status,"Fail")


Narayan
 
You all ROCK! Thank you Thank you! You helped me think through this a little better, and helped me learn more about countifs. However, I decided to add a new helper column to my data that says "Yes" or "No" if the data is relevant and should be counted. Then, I use "Yes" or "No" as a countif criteria! This is conceptual, haven't tried it yet, but I think it will work!
 
You must log in or register to reply here.
Back
Top