[Closed] Can someone tell me what's wrong with this forumla?

More brackets?

=SUMPRODUCT((NAT!F1:F1000=(TODAY()-7))*(NAT!$D$1:$E$1000="design"))

Thanks Dave, didn't fix it but I've just realised that it wouldn't work anyhow, as I want to count all the occurences [b]up to[/b] 7 days previous, so today(),today()-1,today()-2 etc.

Your use of SUMPRODUCT seems to have an implied IF for each array - in my experience it doesn't work like that. Try putting IF statements explicitly - don't know if that will work, but if it does it will allow you to use a "less than" criterion on the date.

If in doubt I always throw more brackets at an Excel formula problem! 😀

When that doesn't work I start stripping bits of the formula out and put it into a different cell, breaking it up to make sure that each part does what I expect it to.

Everything works in the formula, except when trying to change the first array to look at a date range instead of a static value.

Beyond my already limited excel skills here!

Edit: Ah, Greybeard you've probably got something there - ta.

shouldn't you all be building things out of matchsticks?

use the "--" operator to change the logical (TRUE/FALSE) return of an array to a Numerical one (1, 0)

i.e. =SUMPRODUCT(--(NAT!F1:F1000<TODAY()), --((NAT!F1:F1000)>(TODAY()-7)),--(NAT!$d$1:$E$1000="Design"))

I think Stoner has a better handle on arrays than I do - I need to go and learn up on that! I'll go with what he suggests.

Thanks Stoner. My head hurts now and I can't get that to work, giving me a value error.

I [b]am[/b] trying to figure it out! 😕

it might be the $d$1:$E$1000 bit, I didnt test it on that. bear with me a minute.

yeah, it is. WIll fix it. One tick.

=SUMPRODUCT(--(F1:F1000<TODAY()),--((F1:F1000)>(TODAY()-7)),--(D1:D1000="Design"))+SUMPRODUCT(--(F1:F1000<TODAY()),--((F1:F1000)>(TODAY()-7)),--(E1:E1000="Design"))

Not pretty as its the sum of two SUMPRODUCTs because my brain cant get it to work with an OR or a two column array. I shall see if I can simplfy it.

here we are, you can put the OR ("+") bit in one array reference

=SUMPRODUCT(--(F1:F1000<TODAY()),--((F1:F1000)>(TODAY()-7)),--(D1:D1000="Design")+--(E1:E1000="Design"))

so more easily written to see how it works:

=SUMPRODUCT(
--(F1:F1000<TODAY()),
--((F1:F1000)>(TODAY()-7)),

--(D1:D1000="Design")+--(E1:E1000="Design"))

That last bit is getting Excel to work out True or False whether D = Design, converting that to 1s and 0s, and then "adding" a second array of 1s and 0s derived from asking E = Design. That combined array is then multiplied against the first two arrays which are pretty straightforward.

Thanks for this Stoner, but it might be a step too far for a Sunday!

It's giving a count of 0 🙁

The source data is in date format (double checked)

If it's any help, this is the original formula that does what I want it to do, except I want to replace the word "Interfaces" in G, for a date range 7 days prior to today in F (I think you know that though!)

=SUMPRODUCT((NAT!G1:G1000="Interfaces")*(NAT!$D$1:$E$1000="design"))

Completely understand if you've had enoug with this one - you've helped me plent of times previously!

hmm, it's working here.

how about building it up in pieces first. Just try

=SUMPRODUCT(
--(F1:F1000<TODAY()),
--((F1:F1000)>(TODAY()-7)),

first on a shorter range of say 10 sets of dates and see if it comes up with the right number.

ah, try this then

=SUMPRODUCT(((TODAY()-7)<F1:F1000)*(TODAY()>F1:F1000)*($D$1:$E$1000="Design"))

Ha, it's simpy not got the worksheet reference in!

Excellent work as ever 😀

Thank you very much, and for the explanations as well.

EDIT: Ah, that's a reassuringly familar version! Thanks again for you time, =SUMPRODUCT(((TODAY()-7)<NAT!F1:F1000)*(TODAY()>NAT!F1:F1000)*(NAT!$D$1:$E$1000="Design")) works a treat 😀

TBH, I think I ended up overcomplicating it with the double unary ("--").

It doesnt actually need it. My last formula, based on your first, will work - once you;ve put your sheet refs in, naturally 😉

Ewer forumla is rong, it shud beee a formula

HTH. 😉