snowman1976
Board Regular
- Joined
- Nov 4, 2008
- Messages
- 191
hello
i am trying to figure out how to do countifs, and sumifs by comparing two ranges.
I am hoping to do this without a 'helper' column, because I have to do this on a bigger scale.
below is my small example. I want to compare the numbers in the quantity column to the target.
1st I need to COUNT the number of times a quantity is equal or greater than the corresponding target.
2nd I need to SUM if the quantity is less than than the corresponding target.
i am trying to figure out how to do countifs, and sumifs by comparing two ranges.
I am hoping to do this without a 'helper' column, because I have to do this on a bigger scale.
below is my small example. I want to compare the numbers in the quantity column to the target.
1st I need to COUNT the number of times a quantity is equal or greater than the corresponding target.
2nd I need to SUM if the quantity is less than than the corresponding target.
ITEM | Target | Quantity | |
A | 2 | 3 | |
B | 2 | 1 | |
C | 3 | 1 | |
D | 1 | 2 | |
E | 2 | 2 | |
F | 1 | 1 | |
G | 5 | 4 | |
4 | count number of times quantity >= corresponding target | ||
6 | sum of the quantities less than target (not count) | ||