Excel "criteria "NOT"

[wilderness]

I'm assuming that many here are adept at Excel. Perhaps I'm mistaken?

My computer beginnings some 25-years-ago was in spreadsheets, unfortunately, all has been forgotten

I've added some two simple SUMIF's based upon "criteria of a different column" (This is annual and otherwise simple spreadsheet and the SUM lines will expand as new data is added; 2010 ended with 110 lines).

=SUMIF(B1:B16,"HHA",D1:D16)

HHa and HBe are the majority criteria.

I'm wondering if it's possible to use a NOT for these two (within the same criteria) to catch any other straggling criteria and into a 3rd group?

Is it possible to use Regex in Excel?

Thanks in advance.

[Robert Goren]

Watch out for guys dressed in white. They're coming to take you away. HHa, HHa

[wilderness]

Robert,
This is an otherwise simple spreadsheet.

HHa stands for Harness Horse magazine.
HBe stands for Hoof Beats magazine.

As I archive each issue, I document the number of articles and images which have been digitized from each.

The exceptions to these two in my "criteria" column are rare, however there are some and I require a count.
Occasionally I'll do an odd horsey project, real photo's and otherwise.
It's easy enough to do a sort by that column and get the numbers.

I was just hoping for an automatic count ;)

[raybo]

Am I correct that you have a DATE column, and another column that contains the magazine type ("HHa", "HBe", "other"), and another column that contains the numbers you want summed? If so, are you wanting to sum each "type" individually, or as 1 sum? In other words, do you want to know the sum of all "HHa" numbers, a separate sum of "HBe" numbers, and a separate sum for "other" numbers?

A screenshot would help.

[wilderness]

Am I correct that you have a DATE column, and another column that contains the magazine type ("HHa", "HBe", "other"), and another column that contains the numbers you want summed? If so, are you wanting to sum each "type" individually, or as 1 sum? In other words, do you want to know the sum of all "HHa" numbers, a separate sum of "HBe" numbers, and a separate sum for "other" numbers?

A screenshot would help.

Many thanks raybo.

I've no method of loading a file (screenshot).

There are two date columns (one for the issue completion date, and a second for the date of the original publication date), however the dates are NOT used in any of the calculations.

The entire ss is a mere five columns. (six if you count the text column, which is rarely used).

Col 1; completion date
Col 2; name of publication
Col 3; Original publication date
Col 4; # of articles
Col 5; # of images

My SUMIF are on Col's #4 & 5, however based upon the Criteria (publication name) in Col 2. (Currently "HHA" or "HBe").

I was hoping for some method of SUMIF on Col's #4 & 5, however based upon the Criteria and EXCLUDING "HHA" or "HBe" as same criteria.

(as an aside; in Regex, it's a simple exclamation point for "NOT")

[hcap]

I set up a quick example of how to use Sumif.

Criteria in A3 and A 4

Sumif formulas in yellow

[raybo]

Many thanks raybo.

I've no method of loading a file (screenshot).

There are two date columns (one for the issue completion date, and a second for the date of the original publication date), however the dates are NOT used in any of the calculations.

The entire ss is a mere five columns. (six if you count the text column, which is rarely used).

Col 1; completion date
Col 2; name of publication
Col 3; Original publication date
Col 4; # of articles
Col 5; # of images

My SUMIF are on Col's #4 & 5, however based upon the Criteria (publication name) in Col 2. (Currently "HHA" or "HBe").

I was hoping for some method of SUMIF on Col's #4 & 5, however based upon the Criteria and EXCLUDING "HHA" or "HBe" as same criteria.

(as an aside; in Regex, it's a simple exclamation point for "NOT")

Ok, let's make the column numbers, letters instead. So, column 1 would be column A, 2 would be column B, etc.

I think what you are wanting are 6 sums, 2 if it's HHa (1 for articles, 1 for images), 2 if it's HBe (1 for articles, 1 for images), and 2 if it's neither HHa nor HBe (1 for articles, 1 for images). These 6 sums would have to be in 6 separate cells, if I'm understanding that you want 6 separate sums.

Refer to the screenshot for the following formulas (note I didn't include either of the dates, nor did I include any publication names other than HHa and HBe:

For the sum, if it's HHa, and it's articles (formula located in cell H2): =SUMIF(B:B,"HHa",D:D)
For the sum, if it's HHa, and it's images (formula located in cell H3): =SUMIF(B:B,"HHa",E:E)
For the sum, if it's HBe, and it's articles (formula located in cell I2): =SUMIF(B:B,"HBe",D:D)
For the sum, if it's HBe, and it's images (formula located in cell I3): =SUMIF(B:B,"HBe",E:E)
For the sum, if it's neither HHa nor HBe, and it's articles (formula located in cell J2): =SUM(D:D)-(H2+I2)
For the sum, if it's neither HHa nor HBe, and it's images (formula located in cell J3): =SUM(E:E)-(H3+I3)

[raybo]

Well, I see Harry beat me to it, take your pick!

[wilderness]

many thanks Harry and Ray.

Both worked out great.

Thanks again.

[raybo]

many thanks Harry and Ray.

Both worked out great.

Thanks again.

Good deal!

I have a forum specifically for Excel spreadsheets and Excel spreadsheet handicapping. Harry is a member there, too, if you have more spreadsheet questions.

You're more than welcome to join! Here's the link:

Excel Spreadsheet Handicapping (http://alldataexcel.freeforums.org/)