VBA Countif function

3
votes

I need to count items and like using a countif function but I can't get countif to actually work for this instance. I am thinking that I will need to write VBA code for this but am not very familiar with VBA code. Here is a sample of what I am trying to get done

Category    Title     QA Analyst    1    2    3    4    5
NC          E4        Chuck         2    a    3    3    2
C           E6        John          3    1    c    c    d
NC          E3        Jane          b    4    4    5    1
NC          E3        Jane          5    3    4    2    1
C           E6        John          3    c    c    3    1

So category, title, Analyst, and numbers 1-5 are my column headers - on another sheet I have a report - this information used to be in two separate workbooks but I think they can be merged - the only thing that is different is now there is a category column to differentiate the two and before a countif function worked but now I need to differentiate the categories and then count the items. so before my formula was:

=countifs(d2:H10, {"A", "a"})

And this would count all the a's in that array but now I need to differentiate between the categories adn then still do the same count but I am not sure how to get it to differentiate between C and NC for the categories and still count all of the As, Bs, and Cs in the other columns - really could use some help

1
vbaexcelcountif
So you want two separate counts, one for category "NC" and one for category "N"? A pivot table might serve you best also. – CodeJockey
I did have a pivot table at first as it is easy to manipulate and get the information that is wanted but others found this hard to use and understand. I didn't want to keep the information in two seperate workbooks though as this was causing inconsitencies and quality errors. – Filmore34
can you provide an example, i.e. "I need the formula to use these criteria and if it does, it will come up with this result"? This will help us help you. – guitarthrower
Yes, previously this data was being held in two seperate files. I have merged the two and the final report has to differentiate the two categories. The C is for clinical and NC for nonclinical and the 1-5 are audits being performed. The corresponding numbers below this are the scores for that particular audit and if there is text that is due to there not being an audit available that week for that particular individual each letter represents a different reason why the audit wasnt performed. I need to count the number of each reason - there are A-K of reason codes and seperate C from NC – Filmore34
When they were two seperate files, I was able to do this by utilizing a countif function but when i merged the two i needed to seperate C from NC which gave me problems using the Countif function due to having too many arguments – Filmore34

1 Answers

0
votes

You could use SUMPRODUCT:

=SUMPRODUCT(($A$2:$A$10="NC")*($D$2:$H$10="a"))

for example. Note this counts both "a" and "A".