Will SUMPRODUCT work for this?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm currently using the following formula to calculate certain cells in the
range only if they match in two columns. It works fine for getting a grand
total.

=SUMPRODUCT(--Logbook!T3:T65536<>""),--(Logbook!U3:U65536<>""),Logbook!T3:T65536))

But I'd like to be able to break this down by month in the date column.

Ex:

A B C
3-Jan 15 30
5-Jan 8 8
3-Mar 13 19
9-Mar 5 5
7-Jul 10 10
2-Jul 6 4

The values in columns B & C must match in order to be accepted. I'm stuck on
how to break it down by month only.

Not sure if this is important or not but not every row has a date or figure.

As always, help is greatly apprreciated!
 
Do you mean

=SUMPRODUCT(--(MONTH(A1:A100=1),--(B1:B100=C1:C100),B1:B100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Aaron, I had to do something similar to this recently. I got around this by
creating a table that have the 1st day of each month in column A and the last
day of each month in column B. Then the SUMPRODUCT will work as you can check
against column A dn B of your new table and then whatever else you need to.

HTH.
 
Great! That works... but

When I enter the ranges for columns A,B,C I get an error because there are
blank cells in the range. Basically I have a running list of entries for each
row. I'd like to have it set up to compensate for data continuiosly being
added.

Ex: I currently have only 500 rows, I'm predicting that this sheet will grow
into the thousands.

Thanks for the quick response... bonus points!!
 
Do you mean you get a wring count for January? If so use

=SUMPRODUCT(--(A1:A10000<>""),--(MONTH(A1:A10000=1),--(B1:B100=C1:C10000),B1
:B10000)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top