sum(if ? or sumproduct

[whiZZfiZZ]

hi everyone, im stuck again, and was hoping someone might be able t
point me in the right direction to what im doing wrong again.

i have tried to do the following two ways with both gives me tw
different wrong answers with my test data.

=SUMPRODUCT(--(MONTH(Workings!B11:B16)=MONTH(B3)),--(Workings!A11:A16="B2"),--(Workings!G11:G16=A6),(Workings!H11:H16))
(this one gave me 0)

or
the array
=SUM(IF(MONTH(Workings!B11:B16)=MONTH(B3)+(Workings!A11:A16="B2")+(Workings!G11:G16=A6),Workings!H11:H16))

i need to have the first 3 conditions satisfied before i want excel t
add the any matching criteria in the last range.

i got the sum if array to work when i didnt put in the month, part o
the criteria is that the month of the date in cell b3 has to match th
month of any dates in b11:b16.

also the value in B2 is a persons name which is selected via drop dow
list

(you can probably see what im trying to do, the user can select a name
and it will add that persons sales for the month over a different rang
of products, the first product being A6.
The contents of this sheet will then be intergrated into an invoice
have already created.

the value in A6 is also a letter.

will sumproduct work if the matching criteria arent numbers ???

i hope its some simple again that i have missed.

thanks for your help, its really appreciated.

cheer

 

[Dave Peterson]

Without looking too close, try changing this portion:

--(Workings!A11:A16="B2")
to
--(Workings!A11:A16=B2)

With the double quotes, you're actually looking for a name of B2 (not the thing
that's in cell B2--but the characters B2 themselves).

 

[Domenic]

Hi,

Regarding your first formula, your reference to B2 shouldn't b
enclosed within quotes, otherwise it looks for the letters B2 instea
of the person's name.

Also, you may want to change your formula to include year as you
criteria, otherwise you'll get inccorect results if you have blan
cells in your range of dates. And it'll allow you to choose th
correct year, if that's a factor.

Try,

SUMPRODUCT(--(MONTH(Workings!B11:B16)=MONTH(B3)),--(YEAR(Workings!B11:B16)=YEAR(B3)),--(Workings!A11:A16=B2),--(Workings!G11:G16=A6),Workings!H11:H16)

With regards to your second formula, you're using the wrong operator
which should be an * and not a +. And of course, the same thin
applies with respect to your B2 reference and adding the year as
criteria. So try,

=SUM(IF((MONTH(Workings!B11:B16)=MONTH(B3))*(YEAR(Workings!B11:B16)=YEAR(B3))*(Workings!A11:A16=B2)*(Workings!G11:G16=A6),Workings!H11:H16))

Hope this helps

 

[whiZZfiZZ]

thank so you so much Domenic

i guess i was on the right track cept about the "" around B2, don
know how i missed that. arggg.

that was a good pick up about the year been a criteria, thi
spreadsheet will be added to each month thus in 3 years you could hav
3 Julys

thank you very much again.

dont know what i would do without u guy

 

[Domenic]

*thank so you so much Domenic

i guess i was on the right track cept about the "" around B2, don
know how i missed that. arggg.

that was a good pick up about the year been a criteria, thi
spreadsheet will be added to each month thus in 3 years you coul
have 3 Julys

thank you very much again.

dont know what i would do without u guys *

You're very welcome