How to countif a specific column based on a cell text value.

[JamesBlack90]

Hi All,

current_round cell reference is textual and changes depending on the date (it may read "Round 1" "Round 2" etc
Headers along Row 1 and textual information under each header (some cells will be blank). Headers are "Round 1" "Round 2" etc
The information beneath each header is either "Paid" or "Unpaid" (or blank)
What I WANT to be able to do is have a formula which checks 'current_round', and then goes along each header until it finds it, and then counts the number of times 'Paid' appears in that columns and multiplies it by a dollar value to give me the total money paid for the current round (round_cont).

I think I need to do something with INDEX but for the life of me, I can't work out exactly how. I've tried adapting several formulas I've found online but with no luck... The cell ranges (including the headers) are labelled as season_pd_header. (you'll already probably realise from the formula below that information is stored in B1:B50, with the headers in row 1.

My temporary fix is to nest 27 IF statements within eachother, which works, but obviously I don't like my formulas to appear like they were written by a 5 year old, also if season_pd_header happens to get bigger, it makes for a hell of a mess.

In anycase, here is my nested IFs, hopefully it at least helps to illustrate what I am trying to do.

Please Login or Register  to view this content.

Thank you in advance!!!

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[JamesBlack90]

Sorry zbor. Is it ok now?

[zbor]

Yes, it's fine.

[Jacc]

If you post a workbook it will be soo much easier to help you.

[Jacc]

Since you don't provide a sample workbook the results are just guesswork.


If the rounds in the top row go from 1 to 27 then you can try this:

Please Login or Register  to view this content.

If not then try this:

Please Login or Register  to view this content.

[JamesBlack90]

Thank you heaps Jacc!

The second way works perfectly and I was able to replace Tipping!$B$2:$AB$50 with season_pd to allow for future expansion

Thanks again.

[Jacc]

'''''''''