looking up condition in a coloum
Posted by tina on November 14, 2001 10:41 AM
I have a coloum let say A1 with usernames in the b coloum i have a date in the H coloum i have a price on a second sheet i have username in the A coloum B coloum is jan then C is feb then D march ect if i in Bi next to a username in the A coloum i want a formuler that will look at sheet one in coloum A and B in if condition meet the username lets say bubbles2 plus the date jan the sum up numbers in corrosponing H coloum and place in active cell any suggestion how ever small will be appriciated
Posted by Aladin Akyurek on November 14, 2001 12:11 PM
> I have a coloum let say A1 with usernames in the b coloum i have a date in the H coloum i have a price on a second sheet i have username in the A coloum B coloum is jan then C is feb then D march ect if i in Bi next to a username in the A coloum i want a formuler that will look at sheet one in coloum A and B in if condition meet the username lets say bubbles2 plus the date jan the sum up numbers in corrosponing H coloum and place in active cell any suggestion how ever small will be appriciated
In the second sheet
in B2 enter: =SUMPRODUCT((Sheet1!$A2:$A$100=A2)*(TEXT(Sheet1!$B2$B100,"mmm")=B$1),(Sheet1!$H$:$H$100))
Copy this across then down.
Caveat. This formula presupposes that you have text formatted month names in 3-letters like Jan, Feb, Mar in B1, C1, etc. And dates in Sheet1 all belong to the same year.
Aladin
