• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

countifs formula with indirects

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
route217
Asked:
route217
  • 7
  • 4
1 Solution
 
ProfessorJimJamCommented:
when u say red you mean the font color or background color?
0
 
gowflowCommented:
Can you post a sample please ?
gowflow
0
 
route217Author Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gowflowCommented:
=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
 
route217Author Commented:
No error  just returns zero  value.
0
 
gowflowCommented:
sorry but
indirect('SheetName'!)
means nothing

you need to have a cell after !

gowflow
0
 
route217Author Commented:
Sorry c3...and still zero..error  in posting question on my part.
0
 
gowflowCommented:
mmmm YOU from all the people in here should know better than that !!!!

let me see
0
 
gowflowCommented:
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
 
route217Author Commented:
I think it to do with < not begin recongised  as text..
0
 
gowflowCommented:
pls let us think ... we are here for that simply post a sample that has the data.
THANK YOU
gowflow
0
 
gowflowCommented:
got it
replace in your formula
This
"<£1M"

by this
"<1000000"

gowflow
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now