Solved

# sumproduct

Posted on 2011-09-21
314 Views
See attached spreadsheet.  On the totals sheet, I need the yellow cells to count the number of records on sheet "R" column V.  I can't change the format in column V.  I'm sure there is an easy solution but I can't seem to figure it out.

Below is my attempt to try to solve this.  Can you help?

=SUMPRODUCT(--(TEXT('R'!\$V\$2:\$V\$30,"mmmyy")=TEXT(A3,"mmmyy")>0))

Thanks!
test.xls
0
Question by:dddw
• 3

LVL 41

Expert Comment

ID: 36578643
Quick question - are you trying to count number of dates for the month and year, or number of dates (in the other sheet) that match the date in column A (I think the latter, but checking - as your formula converts to MMMYY)...

Dave
0

LVL 6

Expert Comment

ID: 36578660
An array formula can provide you with the solution you are looking for.

If you are not familiar with array functions, you are comparing several things in your range before the formula returns "true".  In this case, you are asking for the month, day, and year to be true before returning "1".  The SUM function adds up all TRUE instances.

When entering an array function, you do not enter the curly brackets.  They are inserted for you when you press CTRL + SHIFT + ENTER after the formula.  So, if you want to copy the formula below, do not include the curly brackets when you highlight the formula.  Paste what you've copied into the cell and press CTRL + SHIFT + ENTER to enter the array formula.
``````{=SUM((MONTH('R'!\$V\$2:\$V\$30)=MONTH(A2))*(DAY('R'!\$V\$2:\$V\$30)=DAY(A2))*(YEAR('R'!\$V\$2:\$V\$30)=YEAR(A2))*1)}
``````
0

LVL 41

Assisted Solution

dlmille earned 500 total points
ID: 36578664
For the individual date count, use this formula:

=SUMPRODUCT(--(A2=DATEVALUE('R'!\$V\$2:\$V\$30)))

A quick way to see if your comparisons will work, setup an equality on a limited number of datapoints... E.g, in a random cell, type:

=A2=DATEVALUE('R'!\$V\$2:\$V\$30)

then select the entire formula and hit F9 - that's how I review to see what the result would be.  Then, it was just a case of wrapping a counting function like SUMPRODUCT with -- to count the TRUE's

See attached...

Dave
test.xls
0

LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 36578669
@dddw - of course, you were on the right track, and although I like to use DATEVALUE to get at the day date, independent of time, you could also have used:

=SUMPRODUCT(--(TEXT(A2,"MMDDYY")=TEXT('R'!\$V\$2:\$V\$30,"MMDDYY")))

Cheers,

Dave
test-r1.xls
0

Author Comment

ID: 36580320
Thank you!  Both of those solutions work great.  I appreciate the information.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Datâ€¦
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.