summing values in certain months

  • Thread starter Thread starter GORDON
  • Start date Start date
G

GORDON

I have used the count number of dates appears in certain months:
=sum((month(a2:a11)=b2)*(year(a2:a11)=c2)*1)
quite successfuly. Now I need to add the values in another column fo
that month. So in other words, search column a2 to a11 with the dat
specified and if there is a value in column d then add all the value
together and give me a total money received for that month. Thi
should be easy, but I seem to be stuck, I have problems with arrays i
that this workbook is shared and will only let me edit arrays if
unshare the workbook. i would appreciate any help anyone can give me.
thanks agai
 
=sum((month(a2:a11)=b2)*(year(a2:a11)=c2)*1*(d2:d11)
ctrl-shift-entered, still

or

=sumproduct(--(month(a2:a11)=b2),--(year(a2:a11)=c2),(d2:d11))
just hit enter normally.
 
First, if you're using the star syntax with SUMPRODUCT, you don't nee
the "*1" bit at the end. The following would suffice...

=SUMPRODUCT((MONTH(A2:A11)=B2)*(YEAR(A2:A11)=C2))

OR

=SUMPRODUCT(--(MONTH(A2:A11)=B2),--(YEAR(A2:A11)=C2))

To calculate the total money received for the specified month...

=SUMPRODUCT(--(MONTH(A2:A11)=B2),--(YEAR(A2:A11)=C2),D2:D11)

Hope this helps!

Edit: Just noticed that you weren't using SUMPRODUCT, but thes
formulas will do what you want without the need to enter them usin
CONTROL+SHIFT+ENTER
 
Domenic said:
Gordon,

It's still not clear to me what it is you're looking for. Can you
explain the criteria involved?
Click to expand...

I needed to count the number of times there is something in column A
and column C and also count the number of times there is somthing in
column A and D so the formula should check to see if there is something
in either column C and D.
 
GORDON said:
I needed to count the number of times there is something in column A and
column C and also count the number of times there is somthing in column
A and D so the formula should check to see if there is something in
either column C and D.
Click to expand...

Try...

=SUMPRODUCT(('PNG-Wellsites'!$A$3:$A$3001>=$C$1)*('PNG-Wellsites'!$A$3:$A$3001<=$F$1)*(('PNG-Wellsites'!$C$3:$C$3001<>"")+('PNG-Wellsites'!$D$3:$D$3001<>"")>0))

Hope this helps!
 
Domenic said:
Try...

=SUMPRODUCT(('PNG-Wellsites'!$A$3:$A$3001>=$C$1)*('PNG-Wellsites'!$A$3:$A$3001<=$F$1)*(('PNG-Wellsites'!$C$3:$C$3001<>"")+('PNG-Wellsites'!$D$3:$D$3001<>"")>0))

Hope this helps!
Click to expand...

This didn't work either.

Let me try again. If there is something in column A and a blank in
column D then count, also if there is something in column A and C then
count. What I'm after is if there is something in A,C, and blank in D
then count all instances. Hope I made myself clearer.
 
GORDON said:
This didn't work either.

Let me try again. If there is something in column A and a blank in
column D then count, also if there is something in column A and C then
count. What I'm after is if there is something in A,C, and blank in D
then count all instances. Hope I made myself clearer.
Click to expand...

'=SUMPRODUCT(--(F6:F3003>=N4),--(F6:F3003<=P4),--(H6:H3003<>""),--(I6:I3003=""))+SUMPRODUCT(--(F$3:F3000>=$N$1),--($F$3:$F$3000<=$P$1)*($I$3:$I$3000=""))'

The above formula works but I thought I could make it simpler.
 
GORDON said:
'=SUMPRODUCT(--(F6:F3003>=N4),--(F6:F3003<=P4),--(H6:H3003<>""),--(I6:I3003=""))+SUMPRODUCT(--(F$3:F3000>=$N$1),--($F$3:$F$3000<=$P$1)*($I$3:$I$3000=""))'

The above formula works but I thought I could make it simpler.
Click to expand...

This has caused me nothing but headaches.

In English, if A is filled and D is blank then count, also if A is
filled and C is filled and D is blank then count.
 
GORDON said:
'=SUMPRODUCT(--(F6:F3003>=N4),--(F6:F3003<=P4),--(H6:H3003<>""),--(I6:I3003=""))+SUMPRODUCT(--(F$3:F3000>=$N$1),--($F$3:$F$3000<=$P$1)*($I$3:$I$3000=""))'

The above formula works but I thought I could make it simpler.

In English, if A is filled and D is blank then count, also if A is
filled and C is filled and D is blank then count.
Click to expand...

Your formula can be shortened as follows...

=SUMPRODUCT(((F6:F3003>=N4)*(F6:F3003<=P4)*(H6:H3003<>"")*(I6:I3003=""))+((F$3:F3000>=$N$1)*($F$3:$F$3000<=$P$1)*($I$3:$I$3000="")))

But I'm now more confused than ever. First, I'm not sure why you have
two different ranges. Secondly, if I use this formula with the example
you provided earlier, it returns 4 instead of 2, as you said you
required. But even that example didn't quite make sense. With this
formula, if A is filled, C is filled, and D is blank, it's counted
twice. Is this what you want?
 
Gordon, if I understand you correctly the following example should yield
3 as your result...


Code:
--------------------
........A...............B................C........ .......D
1 ..... X
2 ..... X............. X
3 ..... X............. X.................X
4 ..... X................................................. .X
5 ..... X..................................X............. X

--------------------


If this is correct, try the following formula...

=SUMPRODUCT(--(((F6:F3003>=N4)*(F6:F3003<=P4)*(H6:H3003<>"")*(I6:I3003=""))+((F$3:F3000>=$N$1)*($F$3:$F$3000<=$P$1)*($I$3:$I$3000=""))>0))

I still don't know why you have two different ranges, but I'm sure
you'll sort it out.

Hope this helps!
 
.........A...............B................C....... ........D
1 ..... X
2 ..... X............. X
3 ..... X............. X.................X
4 ..... X................................................. X
5 ..... X..................................X.............X


Row 1)If A<>"" and D="" then count
Row 2)If A<>"" and B<>"" then don't count
Row 3)If A<>"" and B<>""and C<>"" then count
Row 4)If A<>""and D<>"" then don't count
Row 5)If A<>""and C<>""and D<>"" then don't count

I hope this sample can help, this one seems quite difficult to me. It
will be the base for other similar formulas that I will be
constructing. The end result after counting should be 2 because it
should only fine 2 instances which are true. That is rows 1,3 should
be counted.
 
Back
Top