Multiple Search Criteria "Count"/"SUMPRODUCTIF"

[ugotron]

SUMPRODUCTIF? formula help.

count or sum products that list by week, then by unit, then by section or number 1-6.


I need to be able to filter multiple worksheets same file, for data that has a number 1-6, then filter a second section that lists specific text (between) 6 different organizations, then filter by a work seek of Sunday thru Saturday. and get a count.

For example Week 12
Dog, 1 = 2
Dog, 2 = 1
Dog, 3 = 0
Dog, 4 = 4
Dog, 6 = 7
Cat, 1 = 0
Cat, 2 = 2


then example week 13
and so on....

then example week 14

CPT Ron

 

[ugotron]

Sorry forgot to give some kind of formula with a visual.



=SUMPRODUCT(--(TEXT('sheetname'!$A$2:$A$2000, "DOG"), --('sheetname'!$B$2:$B$2000, "1"), (.....week X or date range....)


CPT Ron

 

[VoG]

Try like this

=SUMPRODUCT(--('sheetname'!$A$2:$A$2000="DOG"), --('sheetname'!$B$2:$B$2000=1), --(sheetname!$C$2:$C$2000>="1/1/2011"+0,--(sheetname!$D$2:$D$2000<="1/3/2011"+0))

 

[ugotron]

I get the formula to count with.

=SUMPRODUCT(--'SHEETNAME'!$a$2:$a$2000>="3/1/2011"+0),--'SHEETNAME'!$B$2:$B$2000="DOG"), --'SHEETNAME'!$C$2:$C$2000="Yes")

But if I try to either +7 days I get incorrect number or if I use second date range but change > to < I get "0" count.

and

if I use another criteria like --'SHEETNAME'!$E$2:$E$2000="1") I get the count of "0"

CPT Ron

 

[VoG]

Try using Excel Jeanie to post a small sample of data and explain what you want to count http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[ugotron]

VoG

I am on a government computer, unable to load extra programs.

Could really use the help here.

two issues.

1. if use second date range, I get incorrect number validated
2. if I search another criteria with the numbers 1-6, I receive value of zero.

CPT Ron

 

[VoG]

If you are trying to count numbers, remove the quotes

--('SHEETNAME'!$E$2:$E$2000=1)

 

[ugotron]

VoG,

removing the " from numbers fixed second problem.

How do I ether set weeks or date range?

CPT Ron

 

[VoG]

I showed you a date range in post #3.

 

[ugotron]

when I use the date range, I get more then I should in quantity.

If I filter just from march 1, my numbers add up.

When i try to range it from week to week quantity is to high. only difference is I added second range



Is there a different way to filter dates or weeks when using multiple search criteria?

CPT Ron