# Sumproduct using column and row

1. ## Sumproduct using column and row

Hi,

I am trying to sum data if the date in column D is before 2014 and if the value in row 2 of that coulmn matches the value in E4 (Q1, Q2...). This is the sum product I am using but it is not picking up any values.

=SUMPRODUCT(('FAC Open POs'!\$D:\$D<DATE(2014,1,1))*('FAC Open POs'!T2:AG2='Cash Waterfall'!E4)*('FAC Open POs'!T:AG))

Thanks,
Justin Picardi

2. ## Re: Sumproduct using column and row

The sum range can't contain any text strings, which apparently it does because row 2 is expected to = "Q1" or "Q2" etc..

So you should specify row #s in the formula.
Try

=SUMPRODUCT(('FAC Open POs'!\$D\$3:\$D\$100<DATE(2014,1,1))*('FAC Open POs'!\$T\$2:\$AG\$2='Cash Waterfall'!E4)*('FAC Open POs'!\$T\$3:\$AG\$100))

3. ## Re: Sumproduct using column and row

My goal is to sum any value in which the date in column D is before 2014 and the Value in Row 2 of that column is equal to the Value in E4(E4 contains "2014_Q1"). That formula had the same result as the one I posted.

4. ## Re: Sumproduct using column and row

Can you post a sample book?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

5. ## Re: Sumproduct using column and row

I built this template of what I want it to do and for some reason this formula worked in here but is not working on the file I am trying to use.

=SUMPRODUCT(('FAC Open POs'!D8:D569<DATE(2014,1,1))*('FAC Open POs'!T2:AG2='Cash Waterfall'!E4)*('FAC Open POs'!T8:AG570))

This formula is giving me an #N/A result

6. ## Re: Sumproduct using column and row

The ranges have to be symetrical

=SUMPRODUCT(('FAC Open POs'!D8:D569<DATE(2014,1,1))*('FAC Open POs'!T2:AG2='Cash Waterfall'!E4)*('FAC Open POs'!T8:AG570))

That should be
=SUMPRODUCT(('FAC Open POs'!D8:D569<DATE(2014,1,1))*('FAC Open POs'!T2:AG2='Cash Waterfall'!E4)*('FAC Open POs'!T8:AG569))

or
=SUMPRODUCT(('FAC Open POs'!D8:D570<DATE(2014,1,1))*('FAC Open POs'!T2:AG2='Cash Waterfall'!E4)*('FAC Open POs'!T8:AG570))

There are currently 1 users browsing this thread. (0 members and 1 guests)