Counting rows with two pieces of information

**Mog666** · 11-25-2007, 12:44 PM

I need a sum to count how many rows have a word from field Alpha in one column as well as a number from field Beta in another column.

Anyone know how I would do this?

Thanks

**VBA Noob** · 11-25-2007, 01:02 PM

See if this helps

Sums range B1 to B10 if A1 to A10 equals Alpha

=SUMPRODUCT(--(A1:A10="Alpha")*(B1:B10))

Note: Sumproduct doesn't allow you to use the whole Column

VBA Noob

**Mog666** · 11-25-2007, 01:11 PM

Thankyou for looking and helping but that isnt looking for field Beta in another column

**VBA Noob** · 11-25-2007, 01:22 PM

Are you saying the word Beta is in Col B and your summing a third column ??

A sample of your data and cols etc would help

VBA Noob

**Mog666** · 11-25-2007, 01:26 PM

Originally Posted by VBA Noob

Are you saying the word Beta is in Col B and your summing a third column ??

A sample of your data and cols etc would help

VBA Noob

Sorry if Im not explaining it well

a third column has no relevence at all, i want to know how many ROWs have Alpha field in Column A as well as Beta Field in column B.

**VBA Noob** · 11-25-2007, 01:30 PM

No example so guessing again

This will count the string Alpha Field in Col A and Beta Field in Col B if there in the same row

=SUMPRODUCT(--(A1:A10="Alpha Field")*(B1:B10="Beta Field"))

VBA Noob

**Mog666** · 11-25-2007, 01:41 PM

Originally Posted by VBA Noob

No example so guessing again

This will count the string Alpha Field in Col A and Beta Field in Col B if there in the same row

VBA Noob

Yay, I think we are nearly there, Thankyou lots.

However I am getting the result 0 but I know some of these rows exist.

Is that sum you gave me just counting columns with the words Alpha Field or is it counting columns with any of the words in the list I have set named Alpha Field?

maybe 'field' isnt the word but I dont know what is, its a while since I last worked on this.

**VBA Noob** · 11-25-2007, 01:45 PM

Post a example so I can see. Can't keep guessing all night

VBA Noob

**Mog666** · 11-25-2007, 02:15 PM

Here is an example of what I meant, 'field' was the wrong word.

see how this list of words has been named Fruitlist

http://www.contextures.com/images/DV07.gif

I need to count how many rows have a word from one list in one column as well as a number from another list in a different column.

**VBA Noob** · 11-25-2007, 02:20 PM

So you have two named ranges called "Alpha" and "Beta" and you want to count when each row isn't blank

=SUMPRODUCT(--(Alpha<>"")*(Beta<>""))

VBA Noob

**Mog666** · 11-25-2007, 02:24 PM

Originally Posted by VBA Noob

So you have two named ranges called "Alpha" and "Beta" and you want to count when each row isn't blank

VBA Noob

You've lost me, however you have helped with half my problem with your previous answer, I just need to change it somehow so thankyou I will continue researching.