SUMPRODUCT or COUNTIF issue

[paulgarton]

Hello,

I am wanting to count the number of records which meet 2 bits of criteria.

To set the scene:
Counting the number of cars which have certain production Year and Model in a Sheet.

1:
Criteria: Must be exact Year (eg. 2012) in cell C6. Range: being named range called "ListYear"

2:
Criteria: Must be any model (eg. Focus) from a named range "Models" again in the named range called "ListModel"

So for 1 successful count the car must be in year "2012" and model being a "Focus"

Note: Ranges ListYear and ListModel are 2 corresponding columns next to each other in one sheet.

I have tried :- but doesn't work!

Please Login or Register  to view this content.

Thanks for your help in advance.

Paul

[FDibbins]

without seeing what you are working with, try something like this...

=countifS(range1,criteria1,range2,criteria2)
so maybe...
=countifS(ListYear,c6,ListModel,model)

[paulgarton]

without seeing what you are working with, try something like this...

=countifS(range1,criteria1,range2,criteria2)
so maybe...
=countifS(ListYear,c6,ListModel,model)

Thanks for your reply.

I have got that working to a specific cell for the "model" criteria but not for a named range.

So maybe I should reask...

How do you use a named range as criteria - so that it can match any of the values in the named range!

Cheers

Paul

[dredwolf]

do the Model list have production years associated with them?
if there is no way to tell if the model was built in the needed year, how can you match it to any SPECIFIC YEAR request?

[FDibbins]

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[paulgarton]

Attached is a smaller version to what I am doing...

Thanks again!

[FDibbins]

ok try this, copied down...
=COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$2)+COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$3)+COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$4)

[oeldere]

Maybe with an pivot table?

See the attached file.

[FDibbins]

happy to help and thanks for the rep

[paulgarton]

ok try this, copied down...
=COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$2)+COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$3)+COUNTIFS(TheList!$A$2:$A$7,A4,TheList!$B$2:$B$7,ModelList!$A$4)

Thanks FDibbins,

Although I want it so that the model list can be expanded easily.

Is there any way to get a named range in there??

Thanks for your input though

Paul

[oeldere]

Yes, that's possible.

Then you get something like this.

Please Login or Register  to view this content.

And a solution with pivot table (see #8)?

[Bob Phillips]

An alternative

=SUMPRODUCT(--(TheList!$A$2:$A$7=Summary!A4),--(ISNUMBER(MATCH(TheList!$B$2:$B$7,ModelList,0))))