Count zero values before specific date

[lesg46]

I have tried looking up other peoples’ questions, but they only want to count
the number that aren’t zero!

I need to count the number of days of zero sales up to the date I have data
for e.g. 21/4/08.

Salesperson 1
Date Product A Product B Total

18/4/08 0 0 0
19/4/08 5 1 6
20/4/08 0 0 0
21/4/08 10 4 14
22/4/08 0
23/4/08 0
etc until end of year

The Total column is a simple formula adding Prod A + B together and copied
down to the end of the year.

I’ve tried using =SUMPRODUCT(--(d3:d296<1)), but obviously this just counts
every zero value formula to the end of the year.

If anyone can help, I’d be forever grateful. I have 35 sales people to do
this for, on a rolling basis, all year!

Thanks in advance
Lesg46

 

[Max]

Try: =SUMPRODUCT((B3:B296<>"")*(C3:C296<>"")*(D3296=0))

 

[Gary''s Student]

You were partially correct:

=SUMPRODUCT((A3:A296<>"")*(D3296=0))

The test on column A removes counts for which no date has been entered yet.

 

[lesg46]

Hi Max,
That does work - thank you. However, by using this formula it does mean I
have to enter all the zeros in their products sold.
Ideally what I'd like to do is leave the cell empty (where they haven't sold
anything), and make the number of zeros calculation based on the zero that
then appears in the formula column. This is where I got to originally, but
then I get the wrong answer as it's looking at the whole year's worth of zero
totals. It just makes the sheet look a bit neater if I don't enter 0 where
they've sold nothing (as there can be many of these!)

Perhaps I wasn't clear in my description originally as I had to put 0's in
the product columns to demonstrate what I meant!

Gary's student reply (thank you) won't work (although I can see the logic),
as I've already pre-filled the year's dates in in Column A.

I've probably completely confused the issue now, but thanks so much for
helping me out.

Lesg

 

[Max]

.. It just makes the sheet look a bit neater if I don't enter 0 where

they've sold nothing (as there can be many of these!)

In which case, this would probably suffice:
=SUMPRODUCT((B3:B296<>"")*(C3:C296<>""))

Take a moment to press the "Yes" button below if it helped

---

 

[Max]

Sorry, dismiss the preceding which is incorrect. Stick with the former. You
have to enter zeros in cols B and C to "denote" the extent.

---

 

[MBSIMON]

GOOD DAY

CAN YOU PLEASE HELP ME WITH THE FOLLOWINFG

PLEASE ANSWER TO (e-mail address removed)


how can i select all possible combinations
from list of SEVERAL columns like column a column b column c column d
column e column f AN G and more
in each column list of differant numbers -in each column could be
diffearnat quantity of numbers
i would like to define ALL variable selection OF NON REPEAABLE COMBINATIONS
from columns example
that from column a select 1 number from b 2 numbers from c 1 number
from d 2 numbers from g 3 numbers or other


coulmn a coulmn b coulmn c coulmn d coulmn e coulmn f coulmn g
coulmn h coulmn i
400 406 410 900 901 1000
10000 300 600
402 915 1001 1012 2500
20000 301 601
404 10012 1003 2501
40000 302 603
409 1010
2502 50000 303 604
405
2555 100000 605
409
RESULTS SUCH AS
400 406 410 900 901 1000 10000 300 600
402 406 410 900 901 1000 10000 300 600
404 406 410 900 901 1000 10000 300 600
AND SO ON ALL COMBINATIONS
now comination from 1 coulmn 2 numbers and mising one number from another
coulmn
400 402 410 900 901 1000 10000 300 600
402 406 410 900 1001 1000 10000 300 600
404 406 915 900 901 1000 10000 300 600
ALSO NEVER TO REPEAT SAME COMBINATION NOT TO BE REPEATED
PLEASE SEND ME YOUR SOULTION IN EXCEL FUNCTION AS I CAN NOT DEAL WITH VBA

THANKS AND BEST REGARDS MOSHE

: