Excel Help

Kick_04

I have a spread sheet where I keep items that I purchase at work. Previously I was only ordering items for 2-3 people, now I am going to be ordering for 12-14 people (3 departments). So I am trying to clean up my spreadsheet some and also make it more functional.

In column G I have "Purchase Order" & in column H I have "Received Date"

What I am wanting to do is count the blank cells in column H. However, with purchasing items from multiple suppliers and multiple months there are spaces to separate each purchase order. So if I just do =COUNTBLANK it will return the wrong number.

I was hoping there was a way to count cells in Column G that had text and if there was text in it to count Column H and see if blank or not, if blank count it, if H has text ignore it.



Example below I want it to count 5&6 but ignore 1,2,3,&4

89094-572, Large DuraFlock Gloves	Case	1	$210.58	$210.58 	10/15/2014	40056968	10/22/2014
14670-149, General Transfer Pipets 	Case	1	$416.46	$416.46 	10/15/2014	40056968	10/27/2014
							
 A-E                          110271							
L15008, 500g Hydroxyacetone 95%	Bottle	1	$83.70	$83.70 	10/28/2014	40057361	
A14740, 2500g Dichloroacetic Acid 99%	Bottle	1	$118.00	$118.00 	10/28/2014	40057361	

Daenris

Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

=IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

Drag that down your spreadsheet, and then sum that column.

There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

MrDelish

countifs(columnG, "="&"", columnH, "<>"&"") will work

Kick_04

Daenris wrote: »

Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

=IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

Drag that down your spreadsheet, and then sum that column.

There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

I copied & pasted your formula into column I and dragged it down. All it does is show "0" for everything.

MrDelish wrote: »

countifs(columnG, "="&"", columnH, "<>"&"") will work

When I copied this formula into Excel all I get is #Name? error. If I change columnG to G4:G100 and the same for columnH it gets rid of the error, but only returns 0.

MrDelish

Is column G genuinely empty or does it have spaces?

Kick_04

I want it to count the yellow squares, not the Red or Light Red squares. The normal spread sheet would not have the colors, added those to see better.

Daenris

Kick_04 wrote: »

Daenris wrote: »

Well, it's pretty much guaranteed that someone will come along with a better solution, but one way would be to first create a new column (say I) with a formula of:

=IF(AND(ISTEXT(G1),ISBLANK(H1)),1,0)

Drag that down your spreadsheet, and then sum that column.

There's a countif and countifs function, but you're very limited on what you can use as criteria expressions, so I don't know that there's a direct way to use them for this.

I copied & pasted your formula into column I and dragged it down. All it does is show "0" for everything.

MrDelish wrote: »

countifs(columnG, "="&"", columnH, "<>"&"") will work

When I copied this formula into Excel all I get is #Name? error. If I change columnG to G4:G100 and the same for columnH it gets rid of the error, but only returns 0.

Hmm. It definitely should work. When you're dragging are the references updating correctly (G2, G3, etc)?

Kick_04

Yeah the 0's in column I is the equation & shows G5/H5 than G6/H6

MrDelish

swap the G and H columns in my formula. My bad, I think

Kick_04

MrDelish wrote: »

swap the G and H columns in my formula. My bad, I think

Thank you. That did it.

Daenris

whoops. I tested with text instead of numbers so the istext function is probably failing. It could be replaced by: NOT(ISBLANK(G1))

Kick_04

Daenris wrote: »

whoops. I tested with text instead of numbers so the istext function is probably failing. It could be replaced by: NOT(ISBLANK(G1))

That does work as well. Thanks, I am going to stick with the other one however. Have a concern I will forget to copy over columnI from year to year with it whited out. Not that big of a problem, just take a moment to figure out why it is no longer counting lol.