Back to Forms in Excel VBA archive index

Back to archive home

I have a spreadsheet that has two columns. I need to add all instances where the first column has the word "network" and second column equals 1. Currently I have a column for "network", a separate column for "network control", another column for "network problem", and yet another column to combine those totals. Is it possible to combine those instances in ONE? I've tried all day and just can't figure it out.

My current formula reads:

=SUM(IF($E$3:$E$401="Network Control Center",IF($F$3:$F$401=J75,1),0))

My current formula reads:

=SUM(IF($E$3:$E$401="Network Control Center",IF($F$3:$F$401=J75,1),0))

sample spreadsheet:

control 5

control 1

network 1

network 2

control 5

network control 1

network control 4

network-problem 1

network 8

Thanks!!!!!!!!!

Check out our Excel Resources | ||||

Lets say that you have all possible descriptions (i.e., control, network, network control, network problem) in one column, say A, and numbers in say column B.

In order to COUNT all occurrences containing the word "network" with a corresponding 1 in B (a case of multiconditional count), use:

=SUMPRODUCT(ISNUMBER(SEARCH("network",A2:A10))*(B2:B10=1))

In order to SUM (i.e., total) the values in B with a corresponding occurence in A that contains the word "network" (a case of multiconditional sum), use:

=SUMPRODUCT(ISNUMBER(SEARCH("network",A2:A10))*(B2:B10))

Given the following data in A2:B10

{"control",5;"control",1;"network",1;"network",2;"control",5;"network control",1;"network control",4;"network problem",1;"network",8}

the count (by the first formula) is: 3

the total is: 27

If you need more, post 10 rows of your data from all relevant columns along with what you want.

Aladin

===================

Thanks for your help Aladin! I really appreciate it!

:

Lets say that you have all possible descriptions (i.e., control, network, network control, network problem) in one column, say A, and numbers in say column B. : My current formula reads

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.