Solved

countifs formula with indirects

Posted on 2014-11-12
12
87 Views
Last Modified: 2014-11-12
Hi Experts  EXCEL 2010

Need a countifs formula to do the following:

If cell A1 sheet apple is red, then look in sheet pivot column b2  ('pivot'!b15:b200) range formula. And count all the reds in the data range and also look in column  g2 ('pivot'!g15;g200) formula..and count the "(blank)"..to return the result.


Apologies  unable to upload sample file.
0
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40437334
when u say red you mean the font color or background color?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437425
Can you post a sample please ?
gowflow
0
 

Author Comment

by:route217
ID: 40437464
Hi how flow
Thanks  for the feedback..nearly worked it out..how would I amend

=countifs(indirect('SheetName'!b3),"red",indirect('SheetName'!),"<£1M")

THE ANSWER is 20 getting 0..cannot see my error
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:gowflow
ID: 40437481
=countifs(indirect('SheetName'!b3),"red",indirect('SheetName'!),"<£1M")

You are missing something after te second SheetName or the ) is wrong what is it ? it gives an error
gowflow
0
 

Author Comment

by:route217
ID: 40437497
No error  just returns zero  value.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437510
sorry but
indirect('SheetName'!)
means nothing

you need to have a cell after !

gowflow
0
 

Author Comment

by:route217
ID: 40437524
Sorry c3...and still zero..error  in posting question on my part.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437569
mmmm YOU from all the people in here should know better than that !!!!

let me see
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437575
Why don't you post this sample workbook and make my life easier !!!!

I tried
=COUNTIFS(INDIRECT(SheetName!B3),"red",INDIRECT(SheetName!C3),"<£1M")

and get #REF! error

please if you need help help us when we ask for sample workbook
gowflow
0
 

Author Comment

by:route217
ID: 40437576
I think it to do with < not begin recongised  as text..
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40437582
pls let us think ... we are here for that simply post a sample that has the data.
THANK YOU
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40437708
got it
replace in your formula
This
"<£1M"

by this
"<1000000"

gowflow
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bar graph 15 43
Conditional fromatting formula 29 32
Compare data between two sheets in Excel 6 31
Excel format formula with percentage 3 6
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question