Formula assistant to count records in two columns based on certain criteria
Hi,
Can any please advise me of the formula I require e.g. VLOOKUP or COUNTIF to do the following:-
Filter on column ‘R’ and select ‘No’
Filter on column ‘F’ and de-select ‘ROM’ and Baseline’
and return the number of records found
Any assistance would be greatly appreciated
Many thanks in advance
Regards
Rob
Rob
I think you forgot to attach example excel file
To attach an Excel file to your post,
- click Go Advanced,
- scroll down until you see Manage Attachments,
- click that and select Browse,
- select your file and click Open,
- click Upload and you will see your attachment below Upload Files from a website
- click Close this window,
- click Submit reply
After that you should see attachment in your post
May be try
Formula:
Please Login or Register to view this content.
Last edited by shukla.ankur281190; 10-27-2017 at 06:03 AM. Reason: missed to read OP
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Maybe like this:
=COUNTIF(R:R,"No")-COUNTIFS(R:R,"No",F:F,"ROM")-COUNTIFS(R:R,"No",F:F,"Baseline")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
EDIT
i see you had replies, while i was composing this
so ignore
so you want to count
the rows where
R = "NO"
F does not equal "ROM" or "Baseline"
theres probably a better way using sumproduct
but
=COUNTIFS(R2:R8,"NO")-COUNTIFS(R2:R8,"NO",F2:F8,"ROM")-COUNTIFS(R2:R8,"NO",F2:F8,"baseline")
that counts the total number of NOs
then how many NOs have ROM
and
how many rows have baseline and takes the away
countifs is using an AND - so to use one formula all must match
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or (maybe):
=COUNTIF(R:R,"No")-SUM(COUNTIFS(R:R,"No",F:F,{"ROM","Baseline"}))
Untested.
Hmmm, no sample, so trying to find out what shall work, I'd opt for simple:
Formula:Please Login or Register to view this content.
it couunts only rows where in R there is "No" and there is neither ROM nor Baseline in column F
Last edited by Kaper; 10-27-2017 at 06:01 AM.
Best Regards,
Kaper
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks