Solved

countifs formula with indirects

Posted on 2014-11-12
12
81 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
  • 7
  • 4
12 Comments
 
LVL 25

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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now