Quick question about SUMIF and COUNTIF with multiple criteria: I've attached an example. Basically, I want to include a cell in the sum if it's value is greater than the MINPRICE value and the AVAIL value for its column is equal to 1. Is there any way to write this in one cell as a concise formula, as opposed to writing seven different SUMIF and COUNTIF functions?
Thanks for any help!
Last edited by montreal1775; 03-05-2010 at 11:16 AM.
How do you get 15 as per your example?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
It's arbitrary, but I included the cell because in the actual workbook the value is based on other cells.
So are you trying to sum all the values in columns B:H where row 53 contains a 1?
Maybe:
=SUMPRODUCT((B53:H53=1)*(B2:H52>B54),B2:H52)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks! That worked. Can I use a similar argument to count the cells that satisfy those criteria?
Yes, just remove the last argument
=SUMPRODUCT((B53:H53=1)*(B2:H52>B54))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks