1. ## Comparing two columns

I have a worksheet with about 500 rows. Among other things, it contains payments received during various numbered time periods. There are 20 different time periods, each with its own number from 1-20. About 25 payments are received during each time period.

Each row of the worksheet corresponds to a payment received during a given time period. I want to enter a period number into an unrelated cell, and have an adjacent cell show the number of payments over \$1,000 received during that period.

Column A is named Period, and contains the number of the time period for each payment.
Column B is named Amount, and contains the amounts of the payments.
Cell C1 is for user input, and will contain the number of one of the periods.
Cell D1 will show the number of payments over \$1,000 received during the period entered in C1.

I need a formula to put in D1. I've played with this pretty much, and I keep coming up with errors. Who can help?

2. Lou,

See if this meets your needs?
=SUMIFS(Amount,Period,\$C\$2,Amount,">1000")
LouS Sumif.JPG
Note: Amount & Period are named ranges b2:b9 & a1:a9 respectively. Using dynamic range names here will improve the spreadsheet's functionality.
Lou Sander SumIfs.xlsx

3. I am using Excel 2003, and I get a #NAME? error. When I load your .xlsx file, Excel tells me that a function on your worksheet is not supported by my version of Excel, and will get a #NAME? error. I'm pretty sure that the SUMIFS function is the problem.

4. Lou,

Sorry, the sumifs function didn't appear until Excel 2007.
We'll probably have to resort to a SumProduct function. I'm not real familiar with them but I know they can do the equivalent of the SumIfs. I'll work on it. If you're lucky Zeddy or Rory will pop in as they are both expert with this function. HTH

5. I have used SUMPRODUCT elsewhere, I believe at the suggestion of Rory, and with the help of an example by him or somebody else. I tried to use it the same way here, but with no success. In the meantime I've done a workaround, but it isn't totally satisfactory.

Every time I think I know Excel, I come up with an application that I don't know how to implement. Sheesh!

6. Lou,

Don't feel bad. I've been working with it and its predecessors for going on 30+ years and I'm still learning.

7. Lou,

Ok I got it.
=SUMPRODUCT((Period=C2)*((Amount>1000)*Amount))
LouS Sumif.JPG

Here's the file: Lou Sander SumIfs.xlsx HTH

8. Originally Posted by RetiredGeek
Lou,

Don't feel bad. I've been working with it and its predecessors for going on 30+ years and I'm still learning.
Me, too. And the really bad part at this point in life is that in the time it takes you to learn one new thing, you forget two or three old ones. Sheesh!

9. Originally Posted by RetiredGeek
Lou,

Ok I got it.
=SUMPRODUCT((Period=C2)*((Amount>1000)*Amount))
LouS Sumif.JPG

Here's the file: Lou Sander SumIfs.xlsx HTH
I still get a #NUM! error. Maybe something else is going on here. I will start again from scratch.

10. =sumproduct((a2:a501=c1)*(b2:b501>=1000))

11. Hi Lou

Try using the array formulas as in attached Excel2003 file.

zeddy

12. =sumproduct((a2:a501=c1)*(b2:b501>=1000)) or

=sumproduct((a2:a501=c1)*(b2:b501>1000))

Tells HOW MANY are over 1000 (or over or equal to 1000) which is what I thought Lou was asking for.

13. Well. After some fooling around, I discovered that my range names for Period and Amount were corrupt. I'd select one of the names by using the drop-down box that shows cell IDs and range names, and a certain group of cells would become selected. Then I'd do it again, and a different group of cells would become selected. Strange business. I fixed it by deleting both the names then redefining them.

Just to be safe, I reformatted all the cells in the areas involved, putting everything in the appropriate numeric format. (You never know when one apparently numeric cell will be inadvertently formatted as Text.)

Everything seems to work now.

The formula that works is =SUMPRODUCT((Period=C2)*(Amount>1000))

Whew!

14. Lou, if you could have some of the period numbers entered as text, try this: =SUMPRODUCT(((1*Period)=C1)*(B2:B501>=1000))

or: =SUMPRODUCT(((1*Period)=C1)*(B2:B501>1000))

15. Hey Y'all

adjacent cell show the number of payments over \$1,000
DUH! Missed that completely! Thanks for setting me straight.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•