LinkBack URL
About LinkBacks
Hello..
I have an issue on the countifs formula where it requires repetitions. Is there any way I can simplify or any approach I will make to get the result. Attaching the sheet I had prepared and the question is at the bottom.
Hello..
I have an issue on the countifs formula where it requires repetitions. Is there any way I can simplify or any approach I will make to get the result. Attaching the sheet I had prepared and the question is at the bottom.
Hi,
Formula:
Please Login or Register to view this content.
Too late now of course but had you designed the table in a normalised/regularised data table you'd have been able to use a Pivot Table to analyse and get all sorts of data results without the need for any formulae
Last edited by Richard Buttrey; 06-10-2020 at 05:13 AM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
The even rows contain 'work, so your formula will never give an above 0 result. However, this might be what you think you want:
=COUNTIFS($K$4:$K$346,"Q",$K$5:$K$347,"J")
Notice the two arrays are offset from each other by one row.
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.
Hi Ali,
Thank you it worked very well. So what that means if the even row contains "work"?
H Richard,
Been thinking using the pivot but this was the format the top management want to see but thanks to your formula. Made my life easier. Great stuff.
Only you can tell us that. It's your data.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
What I meant is this?
(Notice the two arrays are offset from each other by one row.)?
Why do we have to offset it? Thanks.
Because you want to count "J" in the odd rows where the even rows immediately above them contain "Q".
$K$5:$K$347,"J" - odd rows (5, 7, 9, etc.)
$K$4:$K$346,"Q" - even rows (4, 6, 8, etc.)
Excel 2016 (Windows) 32 bit
Sheet: Records (2)
Last edited by AliGW; 06-11-2020 at 01:41 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks