• 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 based on multiple conditions

S

salviakshay

New Member
Hi,


i am trying to do a count if based on 3 conditions...


Currently am using =COUNT(IF((Sheet2!$E$3:$E$12=$B3)*(Sheet2!$C$1:$C$12=Sheet2!$E$2),Sheet1!$F$2:$F$12))as an array.. but it dosent return the count of data from F2:F12.


I have a sample seet that i can send across, dont know how to attach it in this forum.... any help will be appriciated
 
salviakshay


i think your formula is not complete, you have missed the 'value_if_false' in 'if' formula.


regards

sudipta
 
Hi and welcome to the board.


I'm guessing because I can't see your worksheet, but perhaps you actually want to Sum the data in column F rather than Count it? If you're using Excel 2007 or later you can do that with the SUMIFS() function.
 
salviakshay


can you upload the sample file through dropbox? because your logical formula related to 'if' is not clear.


regards

sudipta
 
How about this


=SUMPRODUCT((Sheet2!$E$3:$E$12=$B$3)* (Sheet2!$C$11:$C$12=$E$2)* (Sheet1!$F$2:$F$12="YOUR CONDITION"))
 
Hi Akshay ,


Two things are to be noted in your formula :


1. Since you are entering it as an array formula , using CTRL SHIFT ENTER , the dimensions of the arrays involved in the formula should match i.e. your formula should be written as :


=COUNT(IF((Sheet2!$E$3:$E$12=$B3)*(Sheet2!$C$3:$C$12=Sheet2!$E$2),Sheet1!$F$3:$F$12))


If the array dimensions don't match , you might or might not get the right result , depending on your data.


2. Even with this , the formula has only two conditions ; if you want to add a third condition , you will have to add it as :


=COUNT(IF((Sheet2!$E$3:$E$12=$B3)*(Sheet2!$C$3:$C$12=Sheet2!$E$2)*(third condition),Sheet1!$F$3:$F$12))


Narayan
 
here is the dropbox link


https://www.dropbox.com/s/ighlme35ti376ld/Samp.xlsx?m


A brief description on what i am trying to do... Sheet 1 is going to be my raw data base which will be updated daily... in Sheet 2 in fron for every agent there are 3 coloumns Reject, exchnage and Refunds.. I want the formula to match emp id, week and then give me a count of rejects in reject column, exchnage under exchnage etc...
 
Hi Akshay ,


Try this in F3 :


=SUM(IF((Sheet1!$E$2:$E$12=$B3)*(Sheet1!$C$2:$C$12=$E$2)*(Sheet1!F$2:F$12<>""),1,0))


Enter this as an array formula , using CTRL SHIFT ENTER. Copy it down and across.


Narayan
 
For completeness, here's the
Code: 
COUNTIFS() equivalent (untested)

[pre]=COUNTIFS(Sheet1!$E$2:$E$12,$B3,Sheet1!$C$2:$C$12,$E$2,Sheet1!F$2:F$12,"<>")
[/pre]

------------

Edit by Ninja: COUNTIFS is available only in Excel 2007 and later versions
 
Awsome narayan... this worked perfectly... I had tried count a - countblanks earlier insted of<>... Thank you everyone for assistance
 
Ninjas, someone edited my post and added the text in bold "COUNTIFS is available only in Excel 2007 and later versions". I'm not particularly happy about it because it looks like it was written by me. If you add something to someone's post then you should make it clear that it was written by you and not by them.


If you disagree with this sentiment then please let me know.
 
HI Colin,


Within 1 hour of POST, you can edit your own post.. just Like NINJA's.. :)


Before 1 hour, edit a post by Ninja's is chargeable.. you have still 16 min to update..


Regards,

Deb
 
Hi Colin ,


Sorry if my edit has offended you ; I thought it was a reasonable addition to make , and I thought that keeping it in your name would not be out of place ; however , I will keep this in mind if I make any edits in future.


Narayan
 
Hi Deb, I don't understand your reply. I'm not asking how long I can edit my post for: I'm saying that I am unhappy that someone has injected words into my post and it looks like I wrote them.
 
Hi All,


Thanks a ton for help with above formula, i wanted to check for a little modification in this with a count A condition. Attaching a dropbox link for sample file...


https://www.dropbox.com/s/jd4nclj6owt4s2p/Status%20Report.xlsx?m


I am trying to get a count of "cells with text" in Defect Calculation sheet based on two conditions in Sheet "Status Report". the conditions are the Month is Aug'13 and Queue is "open" in the example it should return 3 in Defect calculation A3.
 
You must log in or register to reply here.
Back
Top