COUNTIF formula is too long...

[kamila04]

Hello everyone,

Can someone please help me to find the solution to this dilemma? I've assigned a project code for each my activity, i.e. from 101D to 299D... Below formula doesn't make sense, it's too long, but I need to find the way to monitor the "D" (development) of project activities. What should I do??? Please advise.

=COUNTIF(D21:AF22,"101D")/2+COUNTIF(D21:AF22,"102D")/2+COUNTIF(D21:AF22,"103D")/2+COUNTIF(D21:AF22,"104D")/2+COUNTIF(D21:AF22,"105D")/2+COUNTIF(D21:AF22,"106D")/2+COUNTIF(D21:AF22,"107D")+COUNTIF(D21:AF22,"108D")/2+COUNTIF(D21:AF22,"109D")/2+COUNTIF(D21:AF22,"110D")/2+COUNTIF(D21:AF22,"111D")/2+COUNTIF(D21:AF22,"112D")/2+COUNTIF(D21:AF22,"113D")/2+COUNTIF(D21:AF22,"114D")/2+COUNTIF(D21:AF22,"115D")/2+COUNTIF(D21:AF22,"116D")/2+COUNTIF(D21:AF22,"117D")/2+COUNTIF(D21:AF22,"118D")/2+COUNTIF(D21:AF22,"119D")/2+COUNTIF(D21:AF22,"120D")/2+COUNTIF(D21:AF22,"200D")/2+COUNTIF(D21:AF22,"202D")/2+COUNTIF(D21:AF22,"204D")/2+COUNTIF(D21:AF22,"206D")/2+COUNTIF(D21:AF22,"208D")/2+COUNTIF(D21:AF22,"210D")/2+COUNTIF(D21:AF22,"212D")+COUNTIF(D21:AF22,"214D")/2+COUNTIF(D21:AF22,"216D")/2+COUNTIF(D21:AF22,"218D")/2+COUNTIF(D21:AF22,"200D")/2+COUNTIF(D21:AF22,"250D")/2

Thanks a lot!

[MarvinP]

Hi kamila04 and welcome to the forum,

Have a look at Pivot Tables to count each of your Development categories.

If you can't figure it out then post a sample workbook and let us have a go at it.

http://www.excelfunctions.net/Excel-Pivot-Tables.html or http://www.csd509j.net/district_info...vot_tables.pdf for examples and instructions.

[kamila04]

Thanks MarvinP,

I have uploaded the table so you can see I mean... Many thanks!

[bebo021999]

Thanks MarvinP,

I have uploaded the table so you can see I mean... Many thanks!

Use this in AC6

Please Login or Register  to view this content.

Copy down and right
As per your example code with COUNTIF, you can divide to 2
=SUMPRODUCT(...)/2
, but I wonder if it wrong?

[MarvinP]

Hi,

I have converted your crosstab table into an EXCEL table with 3 columns only. Then done a pivot table on the data. I think this is what you really want. See the attached to see what I meant when I suggested a Pivit Table. You need to keep your data as an Excel table to be able to do Pivots.

[kamila04]

MarvinP, thanks a lot, great job!

Sorry, I am trying to understand the logic behind it and see how this can be customized to what team needs to see. How your results could be reflected in the monthly "Resource allocation summary"? Could I somehow link the outcome in the other tab and hide the one which contains the formula? I need to understand how many days each staff comitted to D (development) or P (prep), etc on monthly basis regardless of different projects. Please note that first 3 digits are the project/course training codes, and the letter D or P or other letters indicated in the legend means type of work staff did that particular date. Does this sound confusing? Many thanks in advance.

[MarvinP]

Hi kamila04,

The bottom line is that your original format of the data looks great but isn't as useful in Excel. I call your format a CrossTab table that Excel can produce if you format your data correctly. You need to NOT have merged cells, like you do in Col B with your names. The table I have on Sheet1 is much more useful for Excel. I have written code in the past that converts your kind of CrossTab table to a flat or Excel table.

On the attached I've added a new column to the Sheet1 data of Type of Work. Then done a pivot table of what I think you want. See the attached.

[Haseeb Avarakkan]

Hi kamila04,

Does this help???

Or a COUNTIF like,

COUNTIF($D6:$AA7,"*"&AC$5)

[kamila04]

Hey Marvin P and Haseeb A,

THANKS A LOT, both versions that you suggested worked for me! You are guys are awesome, thank you!