Civil Engineering Association
Excel formula help - Printable Version

+- Civil Engineering Association (https://forum.civilea.com)
+-- Forum: Various (https://forum.civilea.com/forum-6.html)
+--- Forum: Free Discussion (https://forum.civilea.com/forum-46.html)
+--- Thread: Excel formula help (/thread-39390.html)



Excel formula help - hmwere - 10-30-2012

Dear fellow CivilEAns,
I would like to count the number of "accepted" or "deferred" automatically using excel formulae.
You may write out , in excel, "accepted" and "deferred" a number of times so you may show me using excel formulae how they may automatically be counted say 5 accepted and 10 deferred. the 5 and 10 should be automatically generated using excel and not manually (because this gets hectic).
An image is appended.

[Image: 59313026758841561309.jpg]

Thanks and regards


RE: Excel formula help - kowheng - 10-30-2012

use formula like below:
=countif(a1:a1000,"Accepted") ===> in cell you'd like to show number of "Accepted"
=countif(a1:a1000,"Deferred") ===> in cell you'd like to show number of "Deferred"


RE: Excel formula help - shadabg - 10-30-2012

I want to know,

[Image: 23489574348107923796.jpg]

if i'm having few Levels written in a single column (as per screenshot) and if i want to reverse it, how can i do in excel.
Sometimes such small things troubles a lot, especially when they in huge nos.

Thanks,


RE: Excel formula help - ingenierobou - 10-30-2012

First select the cells you want to work on.
Then go to DATA-SORT and in the window choose DESCENDING.
In the same window you can choose which column you want to sort first.


RE: Excel formula help - hmwere - 10-31-2012

Dear all,
In continuation with my earlier thread, how can i then know the number of "accepted" or "deferred" that either janet, Dan, Peter, ezra etc each have? Automatically by excel using formulae.

[Image: 23501535877311075986.jpg]

Thanks and regards


RE: Excel formula help - dinu69in - 10-31-2012

Dear hmwere

I haven't done this in Excel before. You have to write a macro to do such operations. However to do this in excel you may have to make some arrangements like in C column from row 1 to 22 you have enter 1. Then from cell A25 onwards you have to list all different names twice. Since there are 9 different names this will fill up for eighteen rows. For each name you have to enter Accepted and Deferred in column B. Then in cell C25 you can enter this formula :
SUMIFS($C$1:$C$22,$A$1:$A$22,A25,$B$1:$B$22,B25). Then click and drag this for entire names. That's (Sad) all....!
Hope this helps... or someone may guide whether this can be done using Pivot tables.


RE: Excel formula help - elbarto_87 - 10-31-2012

Attached is how I usually tackle such a problem. I used nested if functions returning a binary array with I then take the sun of, just remember to use ctrl+shift+enter when entering formulay (google array formula). Sounds more comlicated then it actually is but I find this to be a rather simple solution that is very flexible.

Regards Elabrto

[Image: 68094347848368489001.jpg]