Countif for column with criteria

basienka

New Member
Joined
Sep 9, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Dears,
last time I got great response very fast so I'm trying with other issue I faced.
I have below data:
1631698646184.png

And I need to have it calculated like this:
1631698818418.png

So I need to count how many A/B/C appear in given column. Normally I could use =COUNTIF(A2:A8,H2) and extend the formula to other columns but in database I have one column interesting me and one "empty", one column interesting me and one "empty", ect.

Thank you in advance for your support.
Basia
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I have one column interesting me
Could you explain what you mean by Interesting me?

Secondly, I think you can use this formula =COUNTIF(A2:A8,H2) which you mentioned in your post # 1 even if there are blank rows in a particular column.
 
Upvote 0
Why you don't Use Pivot Tables.
1. Select all Range, Then Go insert Tab, Select PivotTables
2. For Rows Section at Right panel appeared, Drag one of Column Names that have all Letters.
3. add all columns has data at column Section.
4. Right Click on each column name, Select Value setting and rename column to what you want.

Book1.xlsx
ABCDEFGHIJKLM
1
2MQ230Q231MQ232Q233RQ234Q235Row LabelsCount of MQ232Count of MQ230Count of RQ234
3AAA233
4BBBB222
5BACC11
6ACA(blank)1
7AGrand Total566
8ACA
9CB
10
11
12
13
14
Sheet1
 
Last edited:
Upvote 0
This should work
Counts occurrences of H2 in odd number columns
Though snjpverma's question of columns you want and dont want should be answered

=SUMPRODUCT((A2:F8=H2)*(MOD(COLUMN(A2:F8),2)=1))​

 
Upvote 0
Another possible option
Excel Formula:
=COUNTIFS(INDEX(A2:F8,,MATCH(I1,A1:F1,0)),H2)
 
Upvote 0
Solution
Could you explain what you mean by Interesting me?

Secondly, I think you can use this formula =COUNTIF(A2:A8,H2) which you mentioned in your post # 1 even if there are blank rows in a particular column.
column interesting me are the one e.g. MQ230, MQ232, so every second, the one with Priority are not important in this calculation but needed in database.

With pivot I was trying at the begining but it was not working with my database, the first column was counted well but the next one shows the same data. The problem I think is that I do not have column where I have all letters (in the excample I have but in real data not). I create new column to have all answers and put it in row in pivot but still there is sth wrong.
1631703503818.png
 
Upvote 0
Another possible option
Excel Formula:
=COUNTIFS(INDEX(A2:F8,,MATCH(I1,A1:F1,0)),H2)
thanks a lot @Fluff, it is working! Many thanks!

From the other hand if this kind of calculation would be possible to do using pivot it would be the best as I can easily put some filters ect.
 
Upvote 0
Glad to help & thanks for the feedback.

I know nothing about pivots, so cannot help you there.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top