Sumproduct, Countifs, Range help

[lord_kaiser]

HI There,

i require help trying to write a formula

i have the first part working fine as per the below

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!AF:AF"),">"&TaskID!$A25,INDIRECT("'"&SheetList&"'!AF:AF"),"<"&TaskID!$B25))

TaskID A25= -1
TaskID B25= 8

I have 3 sheets and i have placed these sheet names in a named range called SheetList

I now want to add another condition to the above formula where i want to select only certain products as the above formula lists all products.

All the sheets have Col Y with the product names (Apples, Bananas, Oranges, Peaches, Pears)

I have another named range with only certain product names called products. In this range i have Pears, Apples & Oranges.

i want the condition to pull out the total count based on the sheet col y matching the products range name only.

Just can't seem to get it to work.

Any help is appreciated in advance and Thanks

 

[RasGhul]

Try

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!AF:AF"),">"&TaskID!$A25,INDIRECT("'"&SheetList&"'!AF:AF"),"<"&TaskID!$B25,INDIRECT("'"&SheetList&"'!Y:Y"),&TaskID!$Y25))

 

[Marcelo Branco]

Welcome to Mr Excel forum

Question
What is the name and the address of the named range that contains Pears; Apples; Oranges?

M.

 

[lord_kaiser]

Hi Mate,

Thanks for the post.

i think what you meant :

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!AF:AF"),">"&TaskID!$A25,INDIRECT("'"&SheetList&"'!AF:AF"),"<"&TaskID!$B25,INDIRECT("'"&SheetList&"'!Y:Y"),[B]"="&TaskID!$Y25[/B]))

And this works well if it is for 1 value (i.e-Y25). What am i after is a range (Eg: Y25:Y30)

 

[lord_kaiser]

Hi Marcelo,

the named range is called fruitlist

 

[lord_kaiser]

Forgot to add the range:

fruitlist=Workflow!$A$8:$A$15

 

[Marcelo Branco]

Forgot to add the range:

fruitlist=Workflow!$A$8:$A$15

Since FruitList is a vertical range (Workflow!A8:A15) you have to use an array formula with the function TRANSPOSE to convert it to a horizontal range.
So try
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!AF:AF"),">"&TaskID!$A25,INDIRECT("'"&SheetList&"'!AF:AF"),"<"&TaskID!$B25,INDIRECT("'"&SheetList&"'!Y:Y"),TRANSPOSE(FruitList)))
confirmed with Ctrl+Shift+Enter, not just Enter

If it were a horizontal range, for example A8:H8, you could use a regular formula (no need to transpose the named range)

M.

 

[Marcelo Branco]

Remark: for the sake of performance is recommended to use definite ranges in array formulas, not references to entire columns. For example AF2:AF1000 and Y2:Y1000 instead of AF:AF and Y:Y.

M.

 

[lord_kaiser]

Hi Marcelo,

Thanks for the formula. That worked like a charm.

With regards to your 2nd comment about referencing arrays, I using the formula as part of a merge document within our system.

The issue is i am unable to specify a range as after the merge document is complete in the system, we may have 200 records or we may have 10000 records.

Do you recommend any workarounds for this?

Thanks Again

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

About your question.
Use the max possible (likely) number of rows. In your case AF2:AF10000 and Y2:Y10000.

M.