Sumproduct.....
 

Hi there

Cell B1 to B100 have 10 options (subjects), one of which is test
Cell AF1 to AF100 has various dates (say Jan to dec)

I have a countif on the subject column giving me totals of the 10
subjects.

I need to know month by month how many occurences there are in the
subject column - complication is that I need to EXCLUDE the test
occurence, ie so I need a split by date for the remaining three. The
split of the subject within the month is not important - just looking
for total number of occurences of the three subjects within a month

eg
b1 A jan
b2 B feb
b3 A Mar
b4 C Feb
b5 Test Feb
B6 Test Feb

So result of formulae needs to say in Jan there was ONE occurence.
IN Feb there was TWO occurences (not four, ie exclude the two tests...)

Hope this makes sense...

Thanks

D





Think its sumproduct.. but not sure....


*** Sent via Developersdex http://www.developersdex.com ***

Sumproduct.....
 

something like
=sumproduct((rngC="jan")*(rngB<"test"))

--
Don Guillett
SalesAid Software

"Darin Kramer" wrote in message
...


Hi there

Cell B1 to B100 have 10 options (subjects), one of which is test
Cell AF1 to AF100 has various dates (say Jan to dec)

I have a countif on the subject column giving me totals of the 10
subjects.

I need to know month by month how many occurences there are in the
subject column - complication is that I need to EXCLUDE the test
occurence, ie so I need a split by date for the remaining three. The
split of the subject within the month is not important - just looking
for total number of occurences of the three subjects within a month

eg
b1 A jan
b2 B feb
b3 A Mar
b4 C Feb
b5 Test Feb
B6 Test Feb

So result of formulae needs to say in Jan there was ONE occurence.
IN Feb there was TWO occurences (not four, ie exclude the two tests...)

Hope this makes sense...

Thanks

D





Think its sumproduct.. but not sure....


*** Sent via Developersdex http://www.developersdex.com ***

Sumproduct.....
 

Hi Darin,

You guessed right! Assume that months ar in cells A8(Jan), A9(Feb), etc.
In B8 enter

=SUMPRODUCT(--($C$1:$C$6=A8);--($B$1:$B$6<"Test"))

Regards,
Stefi

€žDarin Kramer€� ezt Ã*rta:



Hi there

Cell B1 to B100 have 10 options (subjects), one of which is test
Cell AF1 to AF100 has various dates (say Jan to dec)

I have a countif on the subject column giving me totals of the 10
subjects.

I need to know month by month how many occurences there are in the
subject column - complication is that I need to EXCLUDE the test
occurence, ie so I need a split by date for the remaining three. The
split of the subject within the month is not important - just looking
for total number of occurences of the three subjects within a month

eg
b1 A jan
b2 B feb
b3 A Mar
b4 C Feb
b5 Test Feb
B6 Test Feb

So result of formulae needs to say in Jan there was ONE occurence.
IN Feb there was TWO occurences (not four, ie exclude the two tests...)

Hope this makes sense...

Thanks

D





Think its sumproduct.. but not sure....


*** Sent via Developersdex http://www.developersdex.com ***