MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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