Hi there,
I would appreciate any help...
I have a worksheet with many named tables (the names are given automatically through a VBA script) updated in a regular basis with the same labels at each first row.
Let's say
A1:E12 is TABLE1,
A13:E48 is TABLE2...
A1200:E1261 is TABLE90 etc.
Because all columns' data are similar (ie Column A is Name, Column B is Age, Column C is ID, Column D is Salary, Column E is Children) I've named the entire columns with it's label. So the entire Column A is Name, the entire Column B is Age...
So, for example if I want to find at TABLE45 how many people are aged 45+ with 2 children and salary 15.000, what is the proper syntax of COUNTIFS or any other function?
I tried COUNTIFS(TABLE45;Age>45;Children=2) but it doesn't work.
Any help?
Thanx for your time spend in advance...
Hi teo,
Welcome to the forum.
In Countifs function, you forgot to mention criteria after mentioning it once.
See the syntax below and adjust accordingly, in case of any issue post the sample file
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Thanks dilipandey for your quick response,
let me ask you: if criteria_range1 is TABLE45 then criteria 1 is what? for the rest I understand, criteria_range2 = AGE, criteria2 = >45
thanks again
Hi teo and welcome to the forum
COUNTIFS..in Excel 2003??
Would you like to upload a sample workbook??
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Hi teo,
Basis your example, it would be :-
Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000
Hope this helps.
Like I said before, upload a sample file, if you have any issues
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
I upload for you an example, using SUMPRODUCT function.
Hope to helps you.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
oh thank you guys both!
you saved my day!
sorry for the delay and uploading file, this was my first post
best
t.
You are welcome(Παρακαλώ!!)
Glad to found your solution!
Ths for reb*
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
You are welcome(Παρακαλώ!!)
Glad to found your solution!
Ths for reb*
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Hi teo,
Basis your example, it would be :-
Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000
Hope this helps.
Like I said before, upload a sample file, if you have any issues
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Hi teo,
Basis your example, it would be :-
Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000
Hope this helps.
Like I said before, upload a sample file, if you have any issues
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
I think there are reasons needed to name multi ranges, i.e TABLE1 for list of employees of AA company, TABLE2 for BB company ...Originally Posted by teo_7
As per Fortis1991 in #6, column name (AGE45, SALARY45 ...) were fixed to TABLE45 only.
What happen if seaching to TABLE90 or others?
Therefore, in general, my solution is:
1- Name all the column ACROSS all table (AGE, SALARY, CHILDREN)
2- Crossing the TABLE and column with criteria like this: (TABLE45 AGE>=45)*(TABLE45 SALARY=15000)...then use SUMPRODUCT to count.
So, the formula is:
with H1: Table Name (TABLE45)=SUMPRODUCT((INDIRECT(H1) AGE>=H2)*(INDIRECT(H1) SALARY=H3)*(INDIRECT(H1) CHILDREN=H4))
H2 = Age (45)
H3 = Salary (15000)
H4 = Children (2)
User can change H1,2,3,4 to get expected result.
bebo that was great help,
thanks a lot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks